Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 247386 - Last Review: March 29, 2007 - Revision: 5.1
Microsoft Jet database engine 4.0 enforces the syntax "Is Null"
This article was previously published under Q247386
Novice: Requires knowledge of the user interface on single-user
computers.
This article applies to a Microsoft Access database (.mdb) and to a
Microsoft Access project (.adp).
When you use
= Null instead of
Is Null as the criteria for a query, the query may not return the
expected results. For example, a query may return no records if you use
= Null as the criteria in a field that has records that have no data.
Additionally, a Domain function that uses
= Null in the criteria argument may also not return the expected results
Versions of the Microsoft Jet database engine earlier than
version 4.0 did not correctly enforce the proper syntax
Is Null.
This
behavior is by design.
Steps to Reproduce the Behavior in Access 2003 When You Use a Domain Function
CAUTION: If you follow the steps in this example, you modify the sample
database Northwind.mdb. You may want to back up the Northwind.mdb file and
follow these steps on a copy of the database.
- Start Access.
- On the Help menu, point to Sample Databases, and then click Northwind Sample Database.
- Create a new form, and then add two text boxes.
- Name the text boxes txtTest1 and txtTest2.
- Set the control source of the txtTest1 text box to =DCount("[lastname]","employees","[region] = Null").
- Set the control source of the txtTest2 text box to =DCount("[lastname]","employees","[region] Is Null").
- Open the form in Form view.
Note that in the
txtTest1 box, DCount has returned 0. In the txtTest2 text box, DCount has returned 4.
Steps to Reproduce the Behavior When You Use a Query
- Start Access.
- On the Help menu, point to Sample Databases, and then click Northwind Sample Database.
- In the Database window, click Queries under Object, and then click New.
- In the New Query dialog box, click Design View, and then click OK.
- In the Show Table dialog box, click the Employees table, and then click Add.
- Add the LastName and Region fields to the query design grid.
- Add =Null as the criteria for the Region field.
- On the Run menu, click Run.
Note that no records are returned even though there
are records that do not have Region entries. - Save the query as qryTest.
- Open the qryTest query in Design view.
- Note that Access has optimized the query and has replaced =Null with Is Null.
- On the Run menu, click Run.
Note that no records are returned even though there
are records that do not have Region entries. - In Design View, delete Is Null, and then type Is Null.
- On the Run menu, click Run.
Note that the expected records are returned.
APPLIES TO
- Microsoft Office Access 2007
- Microsoft Office Access 2003
- Microsoft Access 2002 Standard Edition
- Microsoft Access 2000 Standard Edition
| kbprogramming kbdatabase kbprb KB247386 |
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