WHERE (Somefield = [What do you want? (leave blank for all)]
OR [What do you want? (leave blank for all)] is null)
Or if you want them to enter the word All to get all
WHERE (Somefield = [What do you want?]
OR [What do you want?] = "All")
For a more detailed response, post the SQL statement of your query and
indicate which parameter you are concerned about.
A variation on the above is to use
WHERE IIF([What do you want?] Is Null, TRUE, SomeField=[What do you want?])
If you have to do this in the query design view, you use a calculated field
Field: IIF([What do you want?] = "All", TRUE, SomeField=[What do you want?])
Criteria: True
The above options work if your query criteria are not complex, but can fail
with a query too complex error if you do this for multiple columns. Also, the
query can be slow since the second variation does not use any indexes that may
be present.
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
