MS Access 2003: Wild Cards Characters in Parameter Queries

  • Thread starter Thread starter Darrell
  • Start date Start date
D

Darrell

Does a method exist to get a parameter query to return all records matching a
particular criteria? In example, a parameter query prompts for "Manager Last
Name?". Normally you would answer "Doe" and get all results for manager with
the last name of "Doe". Is there anything I can answer to this prompt that
would return all managers?
 
Does a method exist to get a parameter query to return all records matching a
particular criteria? In example, a parameter query prompts for "Manager Last
Name?". Normally you would answer "Doe" and get all results for manager with
the last name of "Doe". Is there anything I can answer to this prompt that
would return all managers?
Click to expand...

A criterion of

LIKE [your parameter]

will accept wildcards; entering

D*

will find all managers whose names begin with D, or

*

will find all non-NULL values.

A criterion of

=[your parameter] OR [your parameter] IS NULL

will return an exact match if the user enters a criterion, and all records if
they leave the parameter control blank.
 
Presumably you've currently got

[Manager Last Name?]

as a criteria under the last name field.

Change that to

[Manager Last Name?] Or ([Manager Last Name?] IS NULL)

(make sure the two reference to [Manager Last Name?] are identical:
copy-and-paste is a good idea!)

Now, simply hitting Enter without picking anything will return all managers.
 
You can use the LIKE operator.

This will give you all managers if you leave the prompt blank or managers
where the Last name starts with whatever you enter.

Field: [ManagerLastName]
Criteria: Like [Manager Last Name?] & "*"

If you want an exact match except when you leave the prompt blank.

Field: [ManagerLastName]
Criteria: Like Nz([Manager Last Name?] ,"*")

OR
You can use the following and enter the "*" when you want all managers

Field: [ManagerLastName]
Criteria: Like [Manager Last Name?]

All those will fail to give you records where the ManagerLastName is null. If
that is a requirement then you can use the following construct. Access query
designer will rearrange the criteria when you save the query.

Field: [ManagerLastName]
Criteria: = [Manager Last Name?] or [Manager Last Name?] Is Null

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top