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

Note:  QODBC Support Wizard is used to test QODBC SQL queries only and is not a development tool.

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 had a transaction 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 QODBC Support Wizard 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 creates 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 are 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