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

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