IIF statement with Dates functions

[texasgrandma]

IIF statement with Dates functions [Solved]

My goal is if the current date = 1st - 4th of the month to return the failure_date if it is >= 1st of the prior month and <= 4th of the current month (ie, 01/01/14 - 02/04/14)

this works:
>=DateSerial(Year(Date()),Month(Date())-1,1) And <=DateSerial(Year(Date()),Month(Date()),4)

But if the current date > 5th of the month to return the failure_date if it is >= 1st of the current month and <= 4th of the next month.(ie 02/01/14 - 03/04/14)

this works
>=DateSerial(Year(Date()),Month(Date()),1) And <=DateSerial(Year(Date()),Month(Date())+1,4)

But when I put it in the iif statement if will not work:

IIf(Date()>DateSerial(Year(Date()),Month(Date()),4),>=DateSerial(Year(Date()),Month(Date()),1) And <=DateSerial(Year(Date()),Month(Date())+1,4),>=DateSerial(Year(Date()),Month(Date())-1,1) And <=DateSerial(Year(Date()),Month(Date()),4))

SQL =
SELECT table_testing_dates.Failure_Date, table_testing_dates.Failure_Date, table_testing_dates.FailureGrouping
FROM table_testing_dates
WHERE (((table_testing_dates.Failure_Date)=IIf(Date()>DateSerial(Year(Date()),Month(Date()),4),(table_testing_dates.Failure_Date)>=DateSerial(Year(Date()),Month(Date()),1) And (table_testing_dates.Failure_Date)<=DateSerial(Year(Date()),Month(Date())+1,4),(table_testing_dates.Failure_Date)>=DateSerial(Year(Date()),Month(Date())-1,1) And (table_testing_dates.Failure_Date)<=DateSerial(Year(Date()),Month(Date()),4))));



Thank you in advance

 

[Brianwarnock]

Hi grandma this grandad is a bit out of touch these days but I think you basically need two criteria in your Where clause which are Or'd , each criteria requires the ANDing of various criteria if you get what I mean, something like the below, hopefully exactly like it

Brian

Where
(Failure_Date between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),4)) and (Date() Between
DateSerial(Year(Date()),Month(Date()),1) and DateSerial(Year(Date()),Month(Date()),4)) Or
(Failure_Date Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,4)) and (Date() >= Dateserial(Year(Date()),Month(Date()),5))

 

[texasgrandma]

Thank you Grand-dad!!! It worked!