I am using Excel to pull info from the InvoiceLine  QB table.  This triggers the 'Microsoft Query' program which looks similar to 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?

Thankyou

You nearly had it. 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

Thanks for your assistance.

I tried it like you said (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?

Again, thank you for your help

Oh,  and btw,  the VB demo form you posted is showing TxnDates that are not yesterday,  its showing everything.  Not sure what that means either.

Thanks

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

Disregard the last two posts,  I got it to work.  Not sure what I was doing wrong,  but when I noticed your txndates I thought that they were for last Nov. and Dec.  When I realized they are for next Nov. and Dec. I went into vb demo,  built it up in pieces  and finally succeeded.  Not sure what I was doing wrong but it works now,  just as you said!  Thankyou!

Question:  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?

Thankyou

I am not sure I understand you.  With respect to the QODBC connections -- should I create one connection for Excel - and then save all my queries that I build to it?

What I have done is created 4 queries in excel and gave them 4 distinct names,  should I have just saved all 4 queries under one name?

And if that is so I would create a different connection for Access,  and save all those queries to it?

Thanks

Saved queries have nothing to do with connections. Any number of queries can use a connection. For more on using Excel see: How do I use prompted date ranges in MS Excel?