ACC1x: Hard-Coded and Passed Wildcards Different in Query

Last reviewed: May 14, 1997
Article ID: Q102431
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:

  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 "*" (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.

  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
Hardware : X86
Issue type : kbbug
Resolution Type : kbfix


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 14, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.