Data Management

10 tricks for handling null values in Microsoft Access

Null values indicate that data is missing or unknown, and if you don't take steps to handle them, you could wind up with runtime errors or erroneous data. These Access pointers will help you understand and effectively address null values in various situations.

This article is also available as a PDF download.

Where errors are concerned, null values are an equal-opportunity menace. If an unhandled null value doesn't generate a runtime error, it'll show up in erroneous data. Neither problem is your run of the mill "oops, there's a bug" error. In fact, an unhandled null value is the sign of a lazy or inexperienced developer. When null values are acceptable values, and they often are, you must handle them upfront and aggressively.

#1: Knowing null


You can't handle a value properly if you don't understand its nature. A common misconception is that a null value is simply an empty field or no value at all. That's not true. A null value indicates that the data is missing or unknown. Occasionally, a null value does mean that the data doesn't exist or isn't valid for that particular record, but the concepts aren't interchangeable.

#2: Dealing with null


Since Access allows null values, it's your job to determine whether you want to store them. Generally, the data will be your best guide. If the nature of the data requires that all data be present to save the record, you can handle null values at the table level. Simply set the field's Required property to Yes and bypass the problem. Be prepared for the rules to change.

Few applications are so tight that nulls aren't present. If users need the flexibility to create records without entering all of the data at the time they create the record, you have a choice. Allow the table to store a null value or use a default expression that stores an appropriate text message, such as "NA" or "Pending."

Unfortunately, this solution works only for text fields. For numeric fields, you could use a default value of 0, but that might cause trouble in the long run because functions handle Null and 0 differently (see #7). In addition, the Default property works only for new records. That means that you can't apply this solution to existing records. The truth is, it's usually easier to handle null values than it is to usurp them in this fashion.

#3: Not equating null


Don't try to find null values by equating them to anything else. The following expressions return an error, regardless of anything's value:

anything = Null
anything <> Null

As far as Access is concerned, Null doesn't equal anything. You can't use the Equals operator (=) to find null values. Nor can you use the Inequality operator (<>) to exclude them. (This isn't always true outside Access.)

#4: Finding or excluding null values


Once you decide that null values are acceptable, it's your job to accommodate them throughout the application. To find or exclude null values, use Is Null and Not Is Null, respectively, in criteria expressions and SQL WHERE clauses. For instance, to find null values in a query, you'd enter Is Null in the appropriate field's Criteria cell. When building a WHERE clause, use Is Null and Not Is Null as follows:

WHERE source.field Is Null
WHERE NOT(source.field) Is Null

Protect VBA expressions from errors by using IsNull()and Not IsNull().For instance, the use of IsNull() in the following If statement handles a potential runtime error when null values exist:

If Not IsNull(field) Then ...

Although Is Null and IsNull() have similar functions, they're not interchangeable.

#5: Working around null


Access won't always work with null values as you might expect. If you allow them, be prepared for surprises. For instance, a simple expression such as

GrandTotal = Subtotal + Shipping

becomes a problem if Shipping contains null values. Instead of returning just the Subtotal, as you might expect, the expression returns Null. That's because any equation that encounters a null value will always return Null. Although it's a nuisance, it makes sense. You can't evaluate an unknown value.

If your data contains null values, use the Nz() function to protect your expressions from this error. Specifically, Nz() returns a value other than Null when it encounters Null as follows:

GrandTotal = Subtotal + Nz(Shipping)

In this case, Nz() returns 0 when Shipping equals Null. Use Nz() in criteria and VBA expressions. Access projects don't support Nz(). Instead, use Transact SQL's IsNull function.

#6: Finding null values using ADO


In # 3, you learned that Null doesn't equal anything. That's true, as long as you're using native functions and VBA. It isn't true if you're manipulating data via the ActiveX Data Object (ADO) library. For instance, the following statement executed against an ADO Recordset object returns an error:

rst.Find "FaxNumber Is Null"

That's because ADO doesn't recognize the Is operator in this context. The ADO library supports the Equals and Inequality operators when searching for or excluding null values. Fortunately, the correction is as simple as replacing the Is operator with the Equals operator:

rst.Find "FaxNumber = Null"

To exclude null values using ADO, use the Inequality operator:

rst.Find "FaxNumber <> Null"

You'll find Access a bit of an oddball on this issue. Many libraries use the Equals and Inequality operators instead of Is. If a non-native library returns an error when working with null values, this switch will probably do the trick.

#7: Understanding the inconsistency of SQL aggregates


Not all aggregate functions consider null values. The good news is, there's a bit of reason to the inconsistency. An aggregate function that evaluates a field does not evaluate null values in its result. However, Count(), First(), and Last() do evaluate null values. It makes sense that they would—just because one field contains a null value doesn't negate the row's purpose within the context of the domain. For instance, Count(*) counts the total number of rows in a recordset even if some of those rows contain null values. If you want to exclude null values in a count, specify the field in the form Count(field). The result of both forms may or may not be the same. The point is, the field-specific form won't consider null values in its count.

#8: Including null values in a conditional search


When using a WHERE clause to find or restrict data, you must explicitly specify null values. Otherwise, Jet excludes the row from the results. This behavior is inherent in the equality issue discussed in #3. Because Null doesn't equal anything, it can't satisfy a condition other than Is Null. For instance, the simple expression

WHERE field < 5

will return all the records where field is less than 5—except for those records where field is Null. Now, that might be what you want, but it might not. If you want to include null values, include Is Null in the condition as follows:

WHERE field < 5 OR field Is Null

#9: Excluding null values in a group


Jet SQL's GROUP BY clause doesn't eliminate null values from the grouped results. Instead, Jet sorts null values to the top or the bottom of the result set, depending on the sort order. For instance, the following query includes records where the Region field is Null:

SELECT FirstName, LastName, Region
FROM Employees
GROUP BY Region

The result isn't right or wrong, it just might not be what you want. You must explicitly exclude null values. In this case, you'd add a HAVING clause as follows:

SELECT FirstName, LastName, Region
FROM Employees
GROUP BY Region
HAVING Not (Region) Is Null

There's no specific method for explicitly excluding null values. The statement's purpose will dictate the solution.

#10: Using null to spot normalization problems


A null value is an acceptable value. However, too many null values often point to an unnormalized table. For instance, if you store customer phone and fax numbers, you might end up with a lot of empty fax number fields. (Even if you have no null values, your table's still not normalized properly, in this case.)

To normalize the phone data, you'd add a table that includes three fields: the foreign key column that relates the phone record to its corresponding customer, the phone number type, and the phone number. The phone number type would identify the phone number as an office, fax, home, cell, and so on. Then, you'd enter phone number records only when appropriate, eliminating null values. If the customer has no fax, there'd be no record for a fax number.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks