Prev: noticia
Next: Allow blanks in Prompt
From: jhicsupt on 7 Apr 2010 03:35
Is there a way to include the blanks?

"Emjay" wrote:

> When prompted for input, I want users to be able to select all. How do I
> accommodate this in the Criteria?
From: John Spencer on 7 Apr 2010 09:04
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

jhicsupt wrote:
> Is there a way to include the blanks?
>
> "Emjay" wrote:
>
>> When prompted for input, I want users to be able to select all. How do I
>> accommodate this in the Criteria?
 | 
Pages: 1
Prev: noticia
Next: Allow blanks in Prompt