Select Query based on day of the week

bluelabel

Board Regular
Joined
Nov 27, 2008
Messages
73
I have a query that every day pulls yesterday's data from an sql database and drops it into Access.

SQL:
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
343
Office Version
365, 2016
Platform
Windows
First, rename your date field...

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

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

bluelabel

Board Regular
Joined
Nov 27, 2008
Messages
73
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

Active Member
Joined
Feb 5, 2018
Messages
343
Office Version
365, 2016
Platform
Windows
Change = to >= and add AND [dtmDateField] < Date(). Fully, like this:

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

bluelabel

Board Regular
Joined
Nov 27, 2008
Messages
73
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,935
Messages
5,483,779
Members
407,410
Latest member
catherinejoy

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top