I have QODBC version v6.00.00.155 and QB05 PRO

I want to retrive the data from PurchaseOrder from 2006-01-01 to 2006-05-04

I have this query in VBA

        .CommandText = "SELECT TxnDate, VendorRefFullName, TotalAmount " _
            + "FROM PurchaseOrder " _
            + "WHERE TxnDate >= {d '2006-01-01'} AND TxnDate <= {d '2006-05-04'}"

The results come out incomplete, there are missing records.

If I put the unoptimized:

   .CommandText = "SELECT TxnDate, VendorRefFullName, TotalAmount " _
            + "FROM PurchaseOrder " _
            + "UNOPTIMIZED WHERE TxnDate = {d '2006-01-01'} AND TxnDate {d '2006-05-04'}"

I get this error: numer 1004, Application-Defined or Object-Definied Error

I have also use the sp_optimizefullsync PurchaseOrder and then run the query in VBDemo 32 an Im still missing some records :(

What could it be wrong?

Your second query wil never work as shown, try:

CommandText = "SELECT TxnDate, VendorRefFullName, TotalAmount " _
            + "FROM PurchaseOrder unoptimized " _
            + "WHERE TxnDate >= {d '2006-01-01'}"

or try using this statement in VB Demo:

SELECT TxnDate, VendorRefFullName, TotalAmount
FROM PurchaseOrder UNOPTIMIZED
WHERE TxnDate >= {d '2006-01-01'}