asked on

access query with unexpected results filtering date field.

Microsoft Access query syntax is giving me unexpected results.
I have an invoice date field.
If the query is run on Monday, I want to see invoices from Friday and the weekend (ie, on Monday I want to see invoices from Friday, Saturday and Sunday).
If the query is run any other day, I want to see invoices from yesterday. (ie on Thursday only want to see invoices from Wednesday)

This query provides no results. If I remove the greater than sign, then I get results, but only for the exact day ( I want to include the weekend details when run on Monday).
IIf(Weekday(Date())=2,>Date()-4,>Date()-1)

This query does work, but it isn't what I want because it excludes the weekend invoices on Monday.
IIf(Weekday(Date())=2,Date()-4,Date()-1)

What am I missing about the '>' function ?

John Tsioumpris

i am thinking that probably is to first day of the week..just define it in the weekday function ....is the 2nd optional parameter

Mark Drelinger

ASKER

Same issue. doesn't yield results with > function.

fully sql :
SELECT ARPAYMENTS.InvoiceDate, ARPAYMENTS.CustNo, ARPAYMENTS.CustomerName, ARPAYMENTS.REF, ARPAYMENTS.INVOICETOTAL, ARPAYMENTS.VALUE_ALLOC, ARPAYMENTS.PostingDate, ARPAYMENTS.rec_no, ARPAYMENTS.ref_alloc2, ARPAYMENTS.rec_no_alloc, ARPAYMENTS.DESCRIPTION, ARPAYMENTS.DocDate, [PostingDate]-[DocDate] AS Age
FROM ARPAYMENTS
WHERE (((ARPAYMENTS.PostingDate)=IIf(Weekday(Date(),0)=2,(ARPAYMENTS.PostingDate)>Date()-4,(ARPAYMENTS.PostingDate)>Date()-1)));

John Tsioumpris

0 is the system default...probably you should 1(vbmonday)

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Drelinger

ASKER

Thank you. It worked perfectly.