Using IIF In Query Criteria

  • Thread starter Thread starter Jeff G
  • Start date Start date
J

Jeff G

All -

I have something I can't quite figure out. It should be simple, but I can't
get one piece of it down.

I have a table (tblSystemOptions) that has a field named EDISTDSKU. It has
a value of Yes, No, and All.

I have a query that I want to run and the returned results should be based
on the criteria mentioned above.

In my criteria box in my Query I have
iif(dlookup("EDISTDSKU","tblSystemOptions")="All",Like "*",...).

What I want to do is base on what's entered in the EDISTDSKU field in
tblSystemOptions to be used as the criteria...
Yes - Return only records with a Yes value
No - Return only records with a No value
All - Return all record.

I can get the Yes and No parts, but what's stumping me is the All.

Any help would be much appreciated.

Jeff
 
The field that the criteria is being used on is a Yes/No, so the criteria
that needs to be passed through is Yes, No, or All (both Yes and No).
 
Your where clause would have to look like this in the SQL view of the query.

(DLookup("EDISTDKSU","tblSystemOptions")="Yes" and SomeField = True)
Or
(DLookup("EDISTDKSU","tblSystemOptions")="No" and SomeField = False)
OR
DLookup("EDISTDKSU","tblSystemOptions")="All"

If you are doing this in the design grid, you will need to enter
DLookup("EDISTDKSU","tblSystemOptions")
in a field block as a calculated field

Then you will need to enter criteria under this field

Field: DLookup("EDISTDKSU","tblSystemOptions")
Criteria(1): = "Yes"
Criteria(2): = "No"
Criteria(3): = "All"

Under your Yes/no field you would have criteria of
Field: YesNoField
Criteria(1): = True
Criteria(2): = False
Criteria(3): = <<Leave Blank>>

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
John -

It does what I need it to do! It took me a minute to get it right, but it
works.

Thanks again.

Jeff
 
Back
Top