IIF with Dateserial and Between

  • Thread starter Thread starter Dan
  • Start date Start date
D

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
 
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()))
 
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
 
Back
Top