Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

Microsoft Jet database engine 4.0 enforces the syntax "Is Null"


View products that this article applies to.

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

↑ Back to the top


Cause

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

↑ Back to the top


Status

This behavior is by design.

↑ Back to the top


More information

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.

  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.

↑ Back to the top


Keywords: KB247386, kbprb, kbdatabase, kbprogramming

↑ Back to the top

Article Info
Article ID : 247386
Revision : 6
Created on : 3/29/2007
Published on : 3/29/2007
Exists online : False
Views : 525