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

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.

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.