IIF statement in query criteria, help!

[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?

 

[Jeff Boyce]

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.

 

[Clifford Bass via AccessMonster.com]

Hi Angela,

Do this in the query designer (showing only necessary fields):

Field Line: last ShiftDate
Criteria Line 1: 1 >=[Start1]
Criteria Line 2: 2 >=[Start2]

In SQL view it would look something like this:

SELECT ...
FROM ...
WHERE ([last] = 1 AND [ShiftDate] >= [Start1]) OR ([last] = 2 AND [ShiftDate]

= [Start2]);

Clifford Bass

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?

 

[KARL DEWEY]

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])

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


--
Build a little, test a little.

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?