Microsoft Jet database engine 4.0 enforces the syntax "Is Null" (247386)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
  • Microsoft Access 2000
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).

SYMPTOMS

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

CAUSE

Versions of the Microsoft Jet database engine earlier than version 4.0 did not correctly enforce the proper syntax Is Null.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce the Behavior 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.

  1. Start Access.
  2. On the Help menu, point to Sample Databases, and then click Northwind Sample Database.
  3. Create a new form, and then add two text boxes.
  4. Name the text boxes txtTest1 and txtTest2.
  5. Set the control source of the txtTest1 text box to =DCount("[lastname]","employees","[region] = Null").
  6. Set the control source of the txtTest2 text box to =DCount("[lastname]","employees","[region] Is Null").
  7. 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

  1. Start Access.
  2. On the Help menu, point to Sample Databases, and then click Northwind Sample Database.
  3. In the Database window, click Queries under Object, and then click New.
  4. In the New Query dialog box, click Design View, and then click OK.
  5. In the Show Table dialog box, click the Employees table, and then click Add.
  6. Add the LastName and Region fields to the query design grid.
  7. Add =Null as the criteria for the Region field.
  8. On the Run menu, click Run.

    Note that no records are returned even though there are records that do not have Region entries.
  9. Save the query as qryTest.
  10. Open the qryTest query in Design view.
  11. Note that Access has optimized the query and has replaced =Null with Is Null.
  12. On the Run menu, click Run.

    Note that no records are returned even though there are records that do not have Region entries.
  13. In Design View, delete Is Null, and then type Is Null.
  14. On the Run menu, click Run.

    Note that the expected records are returned.
Modification Type:MinorLast Reviewed:7/14/2004
Keywords:kbProgramming kbDatabase kbprb KB247386
©2004 Microsoft Corporation. All rights reserved.