Microsoft KB Archive/102431
ACC1x: Hard-Coded and Passed Wildcards Different in Query
The information in this article applies to:
- Microsoft Access versions 1.0, 1.1
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.
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.
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:
- 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:
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 * 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).
Keywords : kbusage
Issue type : kbbug
Last Reviewed: November 4, 2000