IIF with Dateserial and Between

[Dan]

I have a field called COMP_DTE from where I pull date ranges from for a
query. I am using a constant date range of the 22nd to the 21st (buisness
month). Typically I would use:

Between DateSerial(Year(Date()),Month(Date())-1,22) And
DateSerial(Year(Date()),Month(Date()),21)

However I want to use this as a to date as well for weekly info. so on the
27th I would want 22nd - 27th which I can use:

Between DateSerial(Year(Date()),Month(Date()),22) And Date()

Seperatly these work fine but when an IIF statment to select which one to
use I do not get any results. I used the following to test dates and changed
my PC dates:

Test_CMPL_DTE:IIf(DateSerial(Year(Date()),Month(Date()),Day(Date()))>DateSerial(Year(Date()),Month(Date()),22),"Over 22","UNDER 22")

Results came out as they should. Here is my problem if I simply replace the
Over / Under with the above Between statments NADA, Zip, Ziltch, Nothing.

I think I have narrowed it down to the way the IIF expression is set up but
I cant seem to nail it down.

All suggestions and help is very much appreciated and if there is a better
way of approaching this enlightment is always welcome

Cheers

 

[Douglas J. Steele]

Assuming you mean that you put "Between
DateSerial(Year(Date()),Month(Date()),22) And Date()" inside your IIf
statement, you cannot use IIf statements to provide conditions.

In terms of SQL, you'd need something like:

WHERE ((Day(Date()) < 22)
AND (COMP_DTE BETWEEN DateSerial(Year(Date()),Month(Date())-1,22)
AND DateSerial(Year(Date()),Month(Date()),21)))
OR (Day(Date()) >= 22)
AND (COMP_DTE BETWEEN DateSerial(Year(Date()),Month(Date()),22)
AND Date()))

 

[Dan]

AHHHHhhhh I see I think I was looking at it more as a True / False date,
either being >22 or not, instead of a condition. Couldn't see the forrest for
all the trees That worked perfectly, THANKS