I'm having trouble running the following query.

SELECT
   TimeTracking.ClassRefFullName,
   TimeTracking.EntityRefFullName,
   TimeTracking.TxnDate AS StartDate,
   TimeTracking.TxnDate AS EndDate,
  (TimeTracking.DurationMinutes/60) AS DurationHours
FROM TimeTracking
WHERE
 (((TimeTracking.TxnDate)>=[@StartDate] And
   (TimeTracking.TxnDate)<=[@EndDate]) And ((TimeTracking.IsBillable)=1) And ((TimeTracking.IsBilled)=0) And ((Left([TxnID],4))<>(SELECT Left(TxnDeleted.TxnID,4) FROM TxnDeleted WHERE (((TxnDeleted.TxnDelType)="TimeTracking")))));

The issue seems to be when I add the subquery to remove deleted entries from timetracking table.  I'm including the subquery to check for the first (4) char of TxnID from TxnDeleted and checking against TxnID in Timetracking.

Any help would be greatly appreciated.
Chris

I don't understand why you are linking the TxnDeleted table here in the first place? The query should be:

SELECT
   TimeTracking.ClassRefFullName,
   TimeTracking.EntityRefFullName,
   TimeTracking.TxnDate AS StartDate,
   TimeTracking.TxnDate AS EndDate,
  (TimeTracking.DurationMinutes/60) AS DurationHours
FROM TimeTracking
WHERE
 (((TimeTracking.TxnDate)>={d'2007-01-01'} And
   (TimeTracking.TxnDate)<={d'2007-12-01'} And ((TimeTracking.IsBillable)=1) And ((TimeTracking.IsBilled)=0)))

as deleted TimeTracking entries have already been deleted. We use the TxnDeleted table to sync deleted records when the QODBC optimizer updates tables.

Tom,

Thanks for the reply.  I was linking to TxnDeleted because some of the deleted items were still showing up in our query results.  From your response, I'm assuming that anything deleted through QB should have been removed from the items returned by the query.  Perhaps it's more of an optimizer problem and not a query problem.

Chris

If you're not using the US version of QuickBooks, the TxnDeleted table will be empty and deleted records will appear in the optimizer tables. To get arround this you can force the query to execute directly with QuickBooks by using the UNOPTIMIZED tag like this:

SELECT
   TimeTracking.ClassRefFullName,
   TimeTracking.EntityRefFullName,
   TimeTracking.TxnDate AS StartDate,
   TimeTracking.TxnDate AS EndDate,
  (TimeTracking.DurationMinutes/60) AS DurationHours
FROM TimeTracking UNOPTIMIZED
WHERE
 (((TimeTracking.TxnDate)>={d'2007-01-01'} And
   (TimeTracking.TxnDate)<={d'2007-12-01'} And ((TimeTracking.IsBillable)=1) And ((TimeTracking.IsBilled)=0)))

But if you have lots of queries to run, you can manually resync the TimeTracking optimized table to delete deleted records by doing:

sp_optimizefullsync TimeTracking

and still use the optimizer.