Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/102431

From BetaArchive Wiki

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:

  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.

How to Create TestForm:

  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.

How to Pass the Asterisk Wildcard Character as a Parameter:

  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
Issue type : kbbug
Technology :

Last Reviewed: November 4, 2000
© 2001 Microsoft Corporation. All rights reserved. Terms of Use.