ACC1x: Hard-Coded and Passed Wildcards Different in Query

• 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

1. Start Microsoft Access and open the sample database NWIND.MDB.



2. Create a new query based on the Employees table.



3. Add the Last Name and Region fields to the query grid.



4. In the Criteria cell in the Region field of the query grid, type the following:
   
   Like "*"



5. 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.



6. 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.



7. Save the query as TestQuery.

1. In the Database window, create a new, unbound form.



2. Place a text box control on the form.



3. Save the form as TestForm.

1. Open TestForm in Form view and TestQuery in Design view. Arrange the windows so that you can see both the form and the query.



2. Type * in the text box on TestForm and press ENTER. This asterisk will be passed as a parameter for the Like operator in TestQuery.



3. 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 QryParm
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbbug