The information in this article applies to:
- Microsoft Access versions 1.0, 1.1
SYMPTOMS
The Like operator treats hard-coded asterisk (*) wildcard characters
in queries differently from asterisks passed as query parameters.
With the asterisk as a parameter value, queries do not display records
with null values for the field in which the Like criteria is set.
However, with the asterisk as a hard-coded criteria value (for
example, Like "*"), null values do appear for the field with the Like
condition.
STATUS
Microsoft has confirmed this to be a problem in Microsoft Access versions
1.0 and 1.1. This problem no longer occurs in Microsoft Access version 2.0.
MORE INFORMATION
Steps to Reproduce Problem
How to Create TestQuery:
- Start Microsoft Access and open the sample database NWIND.MDB.
- Create a new query based on the Employees table.
- Add the Last Name and Region fields to the query grid.
- In the Criteria cell in the Region field of the query grid, type
the following:
Like "*"
- Run the query.
The result is a dynaset containing nine records, including four
records without values in the Region column (Buchanan, Suyama, King
and Dodsworth). The wildcard character hard-coded with the Like
operator in the query returns all records, whether or not there are
null values in the field.
- Change the Like operator in step 4 above to the following:
Like [Forms]![TestForm]![Field0]
This operator instructs the Like criteria to look in Field0 on a
form called TestForm for the value it will use in the operation.
- Save the query as TestQuery.
How to Create TestForm:
- In the Database window, create a new, unbound form.
- Place a text box control on the form.
- Save the form as TestForm.
How to Pass the Asterisk Wildcard Character as a Parameter:
- Open TestForm in Form view and TestQuery in Design view. Arrange
the windows so that you can see both the form and the query.
- Type "*" (without the quotation marks) in the text box on TestForm
and press ENTER. This asterisk will be passed as a parameter for
the Like operator in TestQuery.
- Run TestQuery.
Note that the query dynaset now contains only five records: those
containing non-null values in the Region field. When the asterisk
is passed as a parameter to the query, Microsoft Access displays
only records with non-null values in the field using the Like
operator (in this case, the Region field).
|