Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 209261 - Last Review: July 15, 2004 - Revision: 1.1
ACC2000: Showing All Records (Including Null) in a Parameter Query
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).
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Â
(http://kbalertz.com/Feedback.aspx?kbNumber=207626/EN-US/
)
ACC2000: Access 2000 Sample Queries Available in Download Center
The following example is based on the sample database Northwind.mdb.
- 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
- 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
- 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
- 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.
- 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.
- 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
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.
APPLIES TO
- Microsoft Access 2000 Standard Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate