ignore saturday and sunday in access queiry

[Guest]

I am using date()-1 to retrieve data from my database in a query. This is
converted into a report which tells me about yesterdays machine performance
in our factory. The problem i have is that on a Monday the report retrieves
Sunday's data when I want it to retrieve Friday's (we run mon - fri). That
means that monday's report is blank.
Each record currently has a date - how do I get the query to ignore sat and
sun and give me friday's record? I dont want to have to type in the correct
date each time so I want the query to do it automatically.

 

[Guest]

You can use the Weekday function inside a bunch of IIF's. Something like this
)untested):

IIF(Weekday(Date()-1)=1, Date()-3, IIF(Weekday(Date()-1)=7,Date()-2,Date()-1))

The only problem I can see with this is that it won't take three-day
weekends or other holiday situations into account. Because of this, I usually
create a calendar table that indiciates whether each date is a working date
or not and then a function that finds the previous working date.

HTH,
Barry