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.
Searching for Null values or zero-length strings
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).
Understanding how Null values affect numeric calculations
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.
Converting Null values to zero
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)
Combining Text fields that contain Null values
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]
Sorting on fields that contain Null values and zero-length strings
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.
Using a wildcard character to return non-Null values
If you use the expression Like "*" (ANSI-89) or Like "%" (ANSI-92) when defining query criteria for a field, the query results will include zero-length strings in that field, but not Null values.