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.
