Query Criteria Not Working Using Weekday Function

[shood38]

I cannot get a query to work. This query is to run automatically only on
weekdays (Mon-Fri)

IF the query runs on a Monday I want it to display Fridays data, otherwise
display data from previous day.

In the criteria spot on the query this is my code:

IIf(Weekday(Date())=2,Date()-3,Date()-1)

Question: What is wrong with above syntax?

 

[Stefan Hoffmann]

hi,

IIf(Weekday(Date())=2,Date()-3,Date()-1)

Question: What is wrong with above syntax?

Nothing, the syntax is correct. Take a look at the OH. Weekday() returns
per defaul 6 for Fridays.

Only Weekday(Date(),vbThursday) returns 2 for Fridays.


mfG
--> stefan <--

 

[John Spencer]

What do you mean by "I cannot get the query to work"?

Do you always get the wrong results? No results? Wrong results on Monday? A
syntax error?

What you posted looks as if it should work. Does your date field contain a
date and time. If you use Now() to populate the field it will have a time and
that means that only records that have the field exactly at midnight will match.

If you are getting now records then as a test try
Between Date()-1 and Date()
and see if that returns records.

You might also try the following:
IIf(Weekday(Date())=2,DateAdd("d",-3,Date()),DateAdd("d",-1,Date())

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County