|Microsoft Access 2002
In Access, a Null value indicates missing data in a field. A field could contain a Null value because the information is not known, or because the field doesn't apply to the record. A Null value is not the same as a value of 0 (zero) or a zero-length string ("") because those values are defined - you know what they are.
Because a Null value is not defined, you do not have enough information about a Null value to compare it to any other value. For example, when you use expressions to specify criteria for a field in a query or an advanced filter, the results will not include Null values in that field. The following example demonstrates this and illustrates the difference between a Null value and a zero-length string:
- Open the Northwind sample database.
The default location of the Northwind sample database is the Program Files\Microsoft Office\Office\Samples folder. If you don't see Northwind.mdb in your Samples folder, or if you need more information about opening Northwind, type open the northwind sample database in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
- Make a copy of the Suppliers table and name the copy SuppliersNull.
- Open the SuppliersNull table and examine the data.
- On the View menu, click Design View.
- In the upper portion of the window, click the Fax field. Set the AllowZeroLength property to Yes.
- Save and close the table.
- Create a query based on the SuppliersNull table.
- Drag the CompanyName and Fax fields to the columns in the query design grid.
- In the Criteria cell for the Fax field, type:
- On the Query menu, click Run.
Note that one record is returned.
- Switch to query Design view, and type Not before the criteria for the Fax field so that it reads:
Not Like "(313)*"
- Run the query.
Note that all of the records returned contain a value in the Fax field. The query doesn't return records that contain Null values because you don't know enough about a Null value to compare it to another value. You cannot be certain that the Fax field in these records does not begin with (313).
- Switch to Design view, delete the criteria, and run the query to return all the records.
- In the Fax field of the first record (Exotic Liquids), type "" (no space between the quotation marks).
This replaces the Null value in this field with a zero-length string.
- Switch to Design view and type Not Like "(313)*" in the Criteria cell for the Fax field.
Now the Exotic Liquids record appears because the Fax field contains a defined value (a zero-length string), and you know that this value does not begin with (313).
For more information about Null values, type blank fields in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.