Knowledgebase
[QODBC-Desktop] How to query Invoice lines where TxnDate=Yesterday using CURDATE
Posted by brad waddell on 12 March 2009 05:21 PM

Troubleshooting: How to query Invoice lines where TxnDate=Yesterday using CURDATE

Problem Description 1

     I am using Excel to pull info from the InvoiceLine QB table. This triggers the 'Microsoft Query' program which looks like an access design grid.

     I am trying to understand how to limit the TxnDate from the InvoiceLine table to yesterday. I tried the following in the SQL dialog box and it does not work, it says "Expected lexical element not found:]"

SELECT InvoiceLine.TxnDate, InvoiceLine.InvoiceLineItemRefListID, InvoiceLine.InvoiceLineItemRefFullName, InvoiceLine.InvoiceLineQuantity
FROM InvoiceLine InvoiceLine
WHERE (InvoiceLine.TxnDate>{CURDATE()-1})
ORDER BY InvoiceLine.InvoiceLineItemRefListID

     Can someone show me the proper way to use this function, or alternatively how to limit the query always to yesterday without explicitly going in and typing the date every day?

Solutions 1

Try:

SELECT InvoiceLine.TxnDate, InvoiceLine.InvoiceLineItemRefListID,
InvoiceLine.InvoiceLineItemRefFullName, InvoiceLine.InvoiceLineQuantity,
{fn CURDATE()} as Today, ({fn CURDATE()}-1) as Yesterday
FROM InvoiceLine InvoiceLine
WHERE (InvoiceLine.TxnDate > {fn CURDATE()}-1) and InvoiceLineItemRefListID is not null
ORDER BY InvoiceLine.InvoiceLineItemRefListID

 

Problem Description 2

     I tried the below (typed it first, then pasted it instead), but I am coming up with the headers but no data. I verified that I have a transaction for yesterday by creating a separate nonfiltered query.

     Also, recall I am using the "Microsoft Query" program that pops up when I invoke Data -> Import External Data -> New Database Query from within Microsoft Excel. the Microsoft query program shouldn't care, should it? Or does it need something installed that I am missing?

Note: Besides, the VB demo form you posted is showing TxnDate that is not yesterday, it is showing everything. Not sure what that means either.

Solutions 2

     For yesterday's invoices only, I would use TxnDate equals, and not TxnDate greater than yesterday:

SELECT InvoiceLine.TxnDate, InvoiceLine.InvoiceLineItemRefListID,
InvoiceLine.InvoiceLineItemRefFullName, InvoiceLine.InvoiceLineQuantity,
{fn CURDATE()} as Today, ({fn CURDATE()}-1) as Yesterday
FROM InvoiceLine InvoiceLine
WHERE (InvoiceLine.TxnDate = {fn CURDATE()}-1) and InvoiceLineItemRefListID is not null
ORDER BY InvoiceLine.InvoiceLineItemRefListID

     As far as the Microsoft Query is concerned, use:

SELECT InvoiceLine.TxnDate, InvoiceLine.InvoiceLineItemRefListID, InvoiceLine.InvoiceLineItemRefFullName, InvoiceLine.InvoiceLineQuantity
FROM InvoiceLine InvoiceLine
WHERE (InvoiceLine.TxnDate>{CURDATE()}-1)
ORDER BY InvoiceLine.InvoiceLineItemRefListID

 

Problem Description 3

     I have been saving each of the queries I have created to a different filename, but I think this is creating multiple applications permissions entries in QuickBooks. They are difficult to discern because all it tells me is 'FLEXquarters QODBC' (there are 5 of these in there now). Should I be doing it this way? or should I save all my queries (all used by this specific front-end Excel app) under one file name?

Solutions 3

     The multiple 'FLEXquarters QODBC' connections shown in Integrated Application is for each type of application you have used. It's normal to have three or more listed, like one for QODBC itself, MS Excel, MS Query etc.

   Saved queries have nothing to do with connections. Any number of queries can use a connection. For more on using Excel see: How to Use Prompted Date Ranges in MS Excel

(166 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).