Using IIF In Query Criteria

[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

 

[Jeff G]

The criteria is a Yes/No field...so the criteria should be Yes, No, or All.

 

[Jeff G]

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

 

[John Spencer]

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
'====================================================

 

[Jeff G]

John -

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

Thanks again.

Jeff