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

Article translations Article translations
Article ID: 209261 - View products that this article applies to.
This article was previously published under Q209261
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

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 that you can use to return all records, including those with null values, when you leave the parameter blank.

NOTE: You can see a demonstration of the technique that is used in this article in the sample file Qrysmp00.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:
207626 ACC2000: Access 2000 Sample Queries Available in Download Center

MORE INFORMATION

The following example is based on the sample database Northwind.mdb.
  1. 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
    					
  2. 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
    					
  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: Forms![Pick Employees]!Region Is Null
    					
  4. Open the query in Design view. On the Query menu, click Parameters. Type Forms![Pick Employees]!Region as the Parameter name, with value as the Data Type.
  5. Open the Pick Employees form, type WA 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
				

REFERENCES

For more information about this topic, click Microsoft Access Help on the Help menu, type like operator in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 209261 - Last Review: July 15, 2004 - Revision: 1.1
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbhowto KB209261

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com