Flexible WHERE SQL Statement in Query

[cparaske]

Hi,

I provide a daily report to my warehouse team that pulls information from a data table for the previous day's activities. Since we don't work on the weekends, my statement needs to be manually modified on Mondays to pull the information from Friday and not from Sunday:
WHERE REF_Cal.Date = Date()-1
becomes
WHERE REF_Cal.Date = Date()-3

Is there a way that I can write the above statement so that it automatically looks at Friday's numbers [i.e. Date()-3] if today is Monday? In Excel I could write a formula to accomplish this as follows:
IF(TEXT(TODAY(),"DDD")="MON",TODAY()-3,TODAY()-1)

Any help will be greatly appreciated!
cparaske

 

[plog]

Check out the WeekDay function: http://www.techonthenet.com/access/functions/date/weekday.php

If Monday, subtract 3 days, otherwise subtract 1.

 

[cparaske]

Thanks, plog!

I used the following statement successfully to get the results I needed:

WHERE REF_Calendar.Date=Date()- IIF(WEEKDAY(DATE()-1)=1,3,1)

I appreciate the guidance!
cparaske