About working with blank fields in queries (MDB)

 Note   The information in this topic applies only to a Microsoft Access database (.mdb).

When a field contains no values, it contains a Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.) value or, for Text (Text data type: In a Microsoft Access database, this is a field data type. Text fields can contain up to 255 characters or the number of characters specified by the FieldSize property, whichever is less.), Memo (Memo data type: In a Microsoft Access database, this is a field data type. Memo fields can contain up to 65,535 characters.), or Hyperlink (Hyperlink data type: A data type for an Access database field that stores hyperlink addresses. An address can have up to four parts and is written using the following format: displaytext#address#subaddress#.) fields, a Null value or a zero-length string (zero-length string: A string that contains no characters. You can use a zero-length string to indicate that you know no value exists for a field. You enter a zero-length string by typing two double quotation marks with no space between them (" ").). 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.

ShowJoining tables whose matching fields contain 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.

ShowSearching 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).

ShowUnderstanding how Null values affect numeric calculations

If you use an aggregate function (aggregate function: A function, such as Sum, Count, Avg, or Var, that you use to calculate totals.) 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 (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.), the Simple Query Wizard, or a custom expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.). 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.

Include records with Null values using the Count function

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.

ShowConverting Null values to zero

When you have fields that contain Null values, you can create an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) 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 (aggregate function: A function, such as Sum, Count, Avg, or Var, that you use to calculate totals.) 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)
            

ShowCombining Text fields that contain Null values

If you're using an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) 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]
            

ShowSorting 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.

ShowUsing a wildcard character to return non-Null values

 
 
Applies to:
Access 2003