Filtering Records based on results of an IIF statement

  • Thread starter Thread starter PeterJ
  • Start date Start date
P

PeterJ

I have an IIF statement in a query that returns "Yes" or "No" depending on
the value of another field.

It works fine, but how do I select either the "Yes" or "No" results. As soon
as I put something in the "Criteria" field in the query it doesn't run.

I know I could do a second query based on the first but I wanted to avoid
this if possible

Thanks

Peter
 
SELECT IIf([TablelName]![SomeFieldName]="Something","Yes","No") AS Result
FROM TablelName
WHERE (((IIf([TablelName]![SomeFieldName]="Something","Yes","No"))="Yes"));


It depends on the Yes and No - if this is a Yes/No field then you need to
use -1 or 0
If its text (You have the result as "Yes" or "No") then don't forget to
enclose the criteria text in commas like this "Yes"
 
Thanks Wayne - I'll have a go at that.

I further narrowed my problem down to the fact that the IIF I was filtering
on was itself dependent on the results of another IIF statement. Seems Access
doesn't like that.

Thanks again

Peter

Wayne-I-M said:
SELECT IIf([TablelName]![SomeFieldName]="Something","Yes","No") AS Result
FROM TablelName
WHERE (((IIf([TablelName]![SomeFieldName]="Something","Yes","No"))="Yes"));


It depends on the Yes and No - if this is a Yes/No field then you need to
use -1 or 0
If its text (You have the result as "Yes" or "No") then don't forget to
enclose the criteria text in commas like this "Yes"


--
Wayne
Trentino, Italia.



PeterJ said:
I have an IIF statement in a query that returns "Yes" or "No" depending on
the value of another field.

It works fine, but how do I select either the "Yes" or "No" results. As soon
as I put something in the "Criteria" field in the query it doesn't run.

I know I could do a second query based on the first but I wanted to avoid
this if possible

Thanks

Peter
Click to expand...
Click to expand...
 
Back
Top