IIF statement in query criteria, help!

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

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.
 
C

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

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.


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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top