IIF statement in query criteria, help!

  • Thread starter Thread starter Angela
  • Start date Start date
A

Angela

I have a query in which one date need to be filtered based on another field.
If the field [last] =1 , the data needs to be filtered showing only data
where [ShiftDate]>=[Start1]. If the field [last]=2, it is
[shiftdate]>=[Start2]. So in the criteria for field [ShiftDate], I entered
"iif([last]=1,[shiftdate]>=[start1],[shiftdate]>=[start2])". I don't get an
error, but I also don't get any results. If I enter ">=[start1]" or
[shiftdate]>=[start1] instead of the iif statement, I get the results
expected. I'm pretty sure that I've used iif statements in criteria before,
but clearly there's something wrong with this one. Can anyone help?
 
Please post the expression you are using...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
If [Last] can only be equal to 1 or 2 then this criteria goes below
[shiftdate] in the design grid --
= IIF([Last]=1, [start1], [start2])
Click to expand...

Or SQL --
WHERE [shiftdate]>= IIF([Last]=1, [start1], [start2])


--
Build a little, test a little.


Angela said:
I have a query in which one date need to be filtered based on another field.
If the field [last] =1 , the data needs to be filtered showing only data
where [ShiftDate]>=[Start1]. If the field [last]=2, it is
[shiftdate]>=[Start2]. So in the criteria for field [ShiftDate], I entered
"iif([last]=1,[shiftdate]>=[start1],[shiftdate]>=[start2])". I don't get an
error, but I also don't get any results. If I enter ">=[start1]" or
[shiftdate]>=[start1] instead of the iif statement, I get the results
expected. I'm pretty sure that I've used iif statements in criteria before,
but clearly there's something wrong with this one. Can anyone help?
Click to expand...
 
Back
Top