Previous Friday Through Current Date

[TJBernard]

My users want to run a query that pulls in all the records from a table where the Create_Date field (Date Type mm/dd/yyyy) is from the previous Friday through the Current Date. So if it is Wednesday, the users want all the data from the previous Friday - Wednesday. The tricky part is, if it is Friday when the query is run, the users would like to pull in all the records from the previous Friday through Thursday and exclude the current Friday. If you have any ideas let me know.

Thank you for your time,

T.J.

 

[Jon K]

Try this criteria for the Create_Date field in the query grid:-

Between IIf(Weekday(Date())=1,Date()-2,IIf(Weekday(Date())=2,Date()-3,IIf(Weekday(Date())=3,Date()-4,IIf(Weekday(Date())=4,Date()-5,IIf(Weekday(Date())=5,Date()-6,IIf(Weekday(Date())=6,Date()-7,Date()-1)))))) And IIf(Weekday(Date()) In (1,2,3,4,5,7),Date(),Date()-1)


Edit:-
The criteria can be simplified as follows:

Between IIF(Weekday(Date())=7, Date()-1, Date()-Weekday(Date())-1) And IIf(Weekday(Date()) In (1,2,3,4,5,7),Date(),Date()-1)