| InvoiceLine - missing records |
| Author |
Message |
|
|
| Posted : 2007-09-24 17:26:00 |
Hi again Tom,
I have been encountering missing invoice records eversince I started using the "InvoiceLine" table. At first, I did not mind the few isolated missing invoice since it only affect only a few.
I extract records using the timemodified period of time using Access:
-----------------------------------------------
Recordset.Open "Select * From InvoiceLine Where TimeModified >= #2007-09-14 01:21:00 PM# and TimeModified < #2038-01-18 01:00:00 AM'}" , oConnection, adOpenDynamic, adLockOptimistic
'then store to array variant
Store_Record_Array = Recordset.GetRows
------------------------------------------------
Based on my data, the records returned from the above statement are : 492 records
I use the VBDemo using the same creteria to eliminate any error with my program:
------------------------------------------------
Select * From InvoiceLine Where
TimeModified >= {ts '2007-09-14 13:21:00.000'} and
TimeModified < {ts '2038-01-18 01:00:00.000'}
----------------------------------------------------
Under the QODBC driver status panel it shows:
Total records found = 491 records
After the query finished it's execution, the query resulted to total 501 rows
My problem is the missing 9 rows of data which are not retreived when in fact there are 501 rows .
Thanks for your help and best regards
|
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-09-24 23:10:33 |
If ever the optimized results are in doubt you should either:
a) Force the call to go directly to QuickBooks using the unoptimized tag:
Select * From InvoiceLine unoptimized Where
TimeModified >= {ts '2007-09-14 13:21:00.000'} and
TimeModified < {ts '2038-01-18 01:00:00.000'}
b) Force the optimized table to be rebuilt using the verify tag:
Select * From InvoiceLine verify Where
TimeModified >= {ts '2007-09-14 13:21:00.000'} and
TimeModified < {ts '2038-01-18 01:00:00.000'} |
|
|
|
|
|
| Posted : 2007-09-25 14:26:13 |
I have been extracting data using unoptimize eversince it was introduced. Neverheless, I tried your suggestion and the result still the same.
Also, I uncheck/unselected the "use optimizer" option under the QODBC setup screen.
Record returned is less than what is really available.
I activated the QODBC "display driver status" panel to check. Using VBDemo, it shows total records as 492 and but the resulting rows showed 501 records- using unoptimized table extraction.
What I get under access is also 492 records no more no less which is equal to what was stated under the QODBC status panel total records-also using unoptimized extraction.
Any other possibilities why I'm getting less records than available?
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-09-25 15:29:02 |
Sorry, everything seems to say there are 492 records, why do you think that there are 501?
How about doing:
Select TxnID from InvoiceLine unoptimized
and
Select TxnID from InvoiceLine verify |
|
|
|
|
|
| Posted : 2007-09-25 19:00:06 |
Using "VB demo" showed all the 509 records/rows although the QODBC driver status indicate total records of 492.
VB Demo - included during the installation of the QODBC driver. I used it to verify and check that my query statement are correct and acceptable by QODBC. Also to verify and check that I am getting the right records.
Thank you for your suggestion using the TxnID, but I need to extract the data using the timemodified method. |
|
|
|
|
|
| Posted : 2007-09-27 17:15:55 |
Is it possible for you to verify if it is really not a bug? - TimeModified extraction of invoiceline table.
Also I noticed that a new version 8 of the QODBC has been release. If I install this on my computer will it affect my QODBC licence even if I don't upgrade QuickBook to version 8.0? Currently, using version 2005.
Part of the revision history of the new QODBC - on the last part - it is stated:
"Corrected an issue with sp_reports and ReportModifiedDateRangeFilter being spelled incorrectly. "
Will this resolve my other unresolve issue? subject matter "possible bug "sp_report CustomTxnDetail" ModifiedDateFrom and ModifiedDateTo " I posted last 09-03-2007 ?
Thanks for your help, Tom.
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-09-27 22:54:10 |
Firstly, there is no bug using later versions of QuickBooks. All testing is done using QuickBooks 2007 and now with 2008. Back in 2004 we did testing with QuickBooks 2005. The only rule with using timestamps is don't use midnight timestamps like '2007-09-14 00:00:00.000', they should be: '2007-09-14 00:00:00.001'. QODBC v8 can be installed over you current installed version without requiring an upgrade.
Secondary, why don't you look at your post: possible bug "sp_report CustomTxnDetail" ModifiedDateFrom and ModifiedDateTo first? |
|
|
|
