Microsoft Access 2010 Bible

Chapter 5: Using Operators and Expressions in Access

207

Logically, a Null is neither True nor False. A Null field is not equivalent to all spaces or to 0.

A Null field simply has no value.

Access lets you work with Null value fields by means of two special operators:

Is Null Is Not Null

You use these operators to limit criteria based the Null state of a field. Earlier in this chapter, you

learned that a Null value can be used to query for products having a picture on file. In the next

example, you look for buyers that don’t have the Notes field filled in:

1. Create a new query using tblCustomers and tblSales.


2. Add Notes and Company from tblCustomers, and SaleDate from tblSales.


3. Enter Is Null as the criteria in the Notes field.


4. Uncheck the Show box in the Notes field.

Your query should look like Figure 5.15. Select the Datasheet View command to see the records

that don’t have a value in the Notes field.

You unchecked the Show box because there is no need to display the Notes field in the query

results. The criteria selects only those rows where Notes is null, so there is, quite literally, nothing

to see in the Notes field and no reason to display it in the results.

FIGURE 5.15

Use Is Null to select rows containing fields that contain no data.