Criteria for query won't accept weekday function with OR statement

Hi All,

I'm trying to get the following criteria to work in a date field: basically, if today is a Sunday or Monday, I want to show some sales from the previous week. If not, I want to show today's and yesterday's sales.

The following is the part in the criteria row, with the problem part highlighted.

IIf(Weekday(Date())=2 Or Weekday(Date())=1,Between (Date()-3) And (Date()),Between (Date()-1) And (Date()))

I know the "true" and "false" parts work - i've tested them separately without the iif statement and I get results for both. The expression Weekday(Date())=7 Or Weekday(Date())=1 seems to be the issue - with the whole statement, I get no entries after I run the query - it results in a blank query. For some reason, it isn't choosing either of the true or false options.

Shouldn't the iif statement choose one of my two options? If it doesn't choose either, shouldn't it tell me there's an error?

Is there something wrong with how I'm using the OR operator in an iif statement?

Thanks,
Daniel

Well, I hoped that would work pjwhitfield but for whatever reason I'm still getting nothing.

I used: IIf((Weekday(Date()))<6,Between (Date()-3) And (Date()),Between (Date()-1) And (Date()))

I checked the "true" and "false" results again just to check, and they both worked by themselves. So (Weekday(Date()))<6 is the problem.

Hmm, this is kinda strange - the following criteria works fine in the date field: IIf(Weekday(Date())<6,42389,42390).

As soon as I threw in a between and and, it failed: IIf(Weekday(Date())<6,(Between 42389 And 42390),42389). I got a blank worksheet.

So you can't use a between and and operator as part of the output of an iff function?

Certainly.

Here's the SQL that doesn't work:

SELECT TBL_Sales.Sales_Order_Number, TBL_Sales.Sales_Order_Date, TBL_Sales.Type, TBL_Sales.Finish, TBL_Sales.Length, TBL_Sales.Options, TBL_Sales.Quantity, TBL_Sales.Model_Number_Lookup
FROM TBL_Sales
WHERE (((TBL_Sales.Sales_Order_Date)=IIf(Weekday(Date())<6,(TBL_Sales.Sales_Order_Date) Between 42389 And 42390,42390)))
ORDER BY TBL_Sales.Sales_Order_Date;

Here's the SQL that does work:

SELECT TBL_Sales.Sales_Order_Number, TBL_Sales.Sales_Order_Date, TBL_Sales.Type, TBL_Sales.Finish, TBL_Sales.Length, TBL_Sales.Options, TBL_Sales.Quantity, TBL_Sales.Model_Number_Lookup
FROM TBL_Sales
WHERE (((TBL_Sales.Sales_Order_Date)=IIf(Weekday(Date())<6,42389,42390)))
ORDER BY TBL_Sales.Sales_Order_Date;

I tried to bold the only significant difference that I saw.

Well, I can't complain - copying and pasting the where clause into the SQL worked perfectly! Thanks so much.

But I don't exactly know what it's doing.

Instead of adding information to the criteria part of the field Sales_Order_Date, it added a calculated field. The field has IIf(Weekday(Date())<6,([TBL_Sales].[Sales_Order_Date]) Between 42389 And 42390,([TBL_Sales].[Sales_Order_Date])=42390) as an expression for the field, with the "Show" checkbox unchecked, and the criteria of <> False

If you've got the time, could you elaborate on what it's doing?