View one or all

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there any way to use a parameter query that allows the user to choose to
view the results for either one specific criteria or view them all?
 
Like [YourCriteriaEntryMethod] & "*"
OR
Like "*" & [YourCriteriaEntryMethod] & "*"

Your criteria entry method could be combobox, listbox, or prompt. If
nothing is selected or entered it will return all.
 
Try this in your query's criteria:
Like IIF(IsNull([Criteria]),"*","*" & [Criteria] & "*")
Apply wildcards to suit your needs.
 
The solutions posted will work as long as your field ALWAYS has data and
never contains a null (empty) value. If you do have nulls, then those
records won't be returned.

Otherwise, you can try one of the following solutions
Assumption: Your field is a text field

Solution 1: Force Yourfield to have a value (a zero-length string is a
value) within the query
Field: NZ(YourField,"")
Criteria: Like Nz([Your Parameter],"*")

Solution 2: (Warning, Access will restructure the following when you save
the query.) It can get too complex to run if you have criteria applied
against many fields.
Field: YourField
Criteria: = [Your Parameter] or [Your Parameter] is Null



kingston via AccessMonster.com said:
Try this in your query's criteria:
Like IIF(IsNull([Criteria]),"*","*" & [Criteria] & "*")
Apply wildcards to suit your needs.

MNY said:
Is there any way to use a parameter query that allows the user to choose
to
view the results for either one specific criteria or view them all?
Click to expand...
Click to expand...
 
Is there any way to use a parameter query that allows the user to choose to
view the results for either one specific criteria or view them all?
Click to expand...

As an alternative to Karl's and Kingston's suggestions (which work
fine for text fields) you can use a criterion

=[Enter search term:] OR [Enter search term:] IS NULL

This can make your query grid look wierd especially if you do it to
multiple fields, but it does work.

John W. Vinson[MVP]
 
Back
Top