.)When a field contains no values, it contains a Null value or, for Text, Memo, or Hyperlink fields, a Null value or a zero-length string. If Null values exist in a field, they can affect the query's results. Following are some guidelines for working with Null values and zero-length strings in queries. (For information on the differences between Null values and zero-length strings, click .)
This topic provides information about:
Joining fields that contain Null values
Searching for Null values or zero-length strings
Understanding how Null values affect numeric calculations
Converting Null values to zero
Combining Text fields that contain Null values
Sorting on fields containing Null values and zero-length strings
Using the asterisk (*) wildcard character to return non-Null values
When you join tables in a query, the result includes only records that don't have Null values in the matching fields. For example, to list Suppliers and Customers who live in the same region, you create a query that includes the Suppliers and Customers tables, and the tables are joined on the Region field. When you view the results, you'll see only the values for records that contain a value in the Region field in both tables.
Return to top
If you're using a query to search for Null values or zero-length strings, type Is Null into the Criteria cell to search for Null values, or type two double quotation marks (" ") into the Criteria cell to search for zero-length strings (don't type a space between the quotation marks).
Return to top
If you use an aggregate function to calculate the sum, average, count, or another amount on a field's values, records with Null values in that field won't be included in the calculation. This is true whether you calculate the aggregate using the Total row in the query design grid, the Simple Query Wizard, or a custom expression. For example, if you use the Count function to count the number of values in a field, it will return a count of all the records with non-Null values. If you want to find the total number of records including those with Null values, use Count with the asterisk (*) wildcard character.
If you use an arithmetic operator (such as +, -, *, /) in an expression (such as [UnitsInStock]+[UnitsOnOrder]), and one of the fields in the expression contains a Null value, then the result of the entire expression will be a Null value.
Return to top
When you have fields that contain Null values, you can create an expression that converts the Null values to zero. You might do this if you want the records containing Null values to be included in an aggregate calculation, or if you want to prevent an expression from resulting in a Null value when a field that's referenced in the expression contains Null values. Use the Nz function, for example, to convert Null values to zero:
Nz([Subtotal],0)+Nz([Freight],0)
For more information about the Nz function, click .
Return to top
If you're using an expression to combine two fields containing text values and one or both of the fields includes Null values, use the & operator instead of the + operator to combine the values. The & operator will combine the values even if they contain Null values, while the + operator will return a Null if either of the two values is a Null. For example:
FullName: [LastName] & " " & [FirstName]
Return to top
When you sort a field in ascending order, any records in which that field contains a Null value are listed first. If a field contains both Null values and zero-length strings, the Null values appear first in the sort order, immediately followed by the zero-length strings.
Return to top
If you use the expression Like "*" when defining query criteria for a field, the query results will include zero-length strings in that field, but not Null values.
Return to top
Ways to perform calculations in a query
About aggregate functions and other options in the query design grid’s Total row
Specify a sort order for a query