Article ID: 103181 - Last Review: January 18, 2007 - Revision: 2.1

ACC: Showing All Records (Including Null) in a Parameter Query

Retired KB ArticleRetired KB Content Disclaimer
View products that this article applies to.
This article was previously published under Q103181
Moderate: Requires basic macro, coding, and interoperability skills.
Expand all | Collapse all

SUMMARY

When you run a query that takes its parameters from a form, no records are returned by the query if you leave the field blank. If you type an asterisk (*) in the field, only records with non-Null values are returned.

This article describes a method you can use to return all records, including those with Null values, when you leave the parameter blank.

NOTE: A demonstration of the technique used in this article can be seen in the sample file, Qrysmp97.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:
182568  (http://support.microsoft.com/kb/182568/EN-US/ ) ACC97: Microsoft Access 97 Sample Queries Available in Download Center
Back to the top

MORE INFORMATION

The following example is based on the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 2.0 or earlier).
  1. Create the following form not based on any table or query: 
          Form: Pick Employees
      --------------------
      Control: Textbox
         ControlName: Region
      Control: Command Button
         Caption: Run Query
         OnClick: Run Employee Query

    NOTE: In Microsoft Access 1.x, the Onclick property is called the OnPush property.
  2. Create the following macro: 
          Macro Name           Action
      ------------------------------
      Run Employee Query   OpenQuery

      Run Employee Query Actions
      --------------------------
      OpenQuery:
         Query Name: Employee Query
         View: Datasheet
         Data Mode: Edit

  3. Create the following query based on the Employees table: 
          Query: Employee Query
      ---------------------
      Field: First Name
         Show: True
      Field: Last Name
         Show: True
      Field: Region
         Show: True
         Criteria: Like Forms![Pick Employees]!Region & "*"
         Or: <leave blank>
      Field: Forms![Pick Employees]!Region
         Show: False
         Criteria: <leave blank>
         Or: Is Null

  4. Open the query in Design view. On the Query menu, click Parameters. Type "Forms![Pick Employees]!Region" (without the quotation marks) as the parameter name, with VALUE as the data type.

    NOTE: If you have Microsoft Access 1.x or 2.0 with the Microsoft Jet database engine version 2.0, define your parameter as TEXT rather than VALUE.
  5. Open the Pick Employees form, type "WA" (without the quotation marks) in the Region field, and click the Run Query button. Note that the result set contains five employee names.
  6. Open the Pick Employees form, clear the Region field, and click the Run Query button again. Note that the result set now contains nine employee names, four with blank region codes.
By adding the parameter as a field, you can test the parameter and control the other criteria. The equivalent SQL Where condition is as follows: 
   Where Region Like Forms![Pick Employees]!Region & "*"

      Or Forms![Pick Employees]!Region Is Null
Back to the top

REFERENCES

For more information about this topic, search the Help Index for "like operator," or ask the Microsoft Access 97 Office Assistant.
Back to the top
APPLIES TO
  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
Back to the top
Keywords: 
kbhowto KB103181
Back to the top
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top
 

Get Help Now

Contact a support professional by E-mail, Online, or Phone

Article Translations