Select Query based on day of the week

[bluelabel]

I have a query that every day pulls yesterday's data from an sql database and drops it into Access.

SELECT Date, InvoiceNo, Customer


FROM SalesDatabase


WHERE Date=Yesterdaysdate

What I need it to do though is when today's date is a Monday, I need it to gather the data for Friday, Saturday and Sunday, otherwise run the standard code.

So on a monday we have a special code that runs and picks up the last three days worth of data, every other day we want it to just pick up yesterdays data.

I hope that makes sense.

Thanks

 

[JonXL]

First, rename your date field...

Then try testing for the current day of week and use that to determine what to subtract.

WHERE [dtmDateField] = DATE()-IIF(WEEKDAY(DATE(),2)=1,3,1)

Weekday Function - Microsoft Support

support.microsoft.com

 

[bluelabel]

Hi @JonXL,

Thank you and that has worked.

The extension of this is when the day is a Monday I need it to pick up a date range of Friday-Sunday. In the formula given it picks up the Friday only.

How do I get it to pick up the date range of say 26/06 - 28/06 inclusive please and thank you.

 

[JonXL]

Change = to >= and add AND [dtmDateField] < Date(). Fully, like this:

WHERE [dtmDateField] >= DATE()-IIF(WEEKDAY(DATE(),2)=1,3,1) AND [dtmDateField] < Date()

 

[bluelabel]

Of Course, the AND was what I was missing. I'm getting used to writing SQL code rather than Excel

Thank you so much @JonXL