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.

15 comments
Rob C
Rob C

You pick on someone for using VB6, and you are writing code INTO A DATA BASE. You should be hanging your head in shame and using a DB Just as a DB, and do your programming with a separate programming language. Rob PS If you are using a programming language, then I apologize. Please then pass my shock and horror to others that are writing code into a DB.

lightroses
lightroses

Hello to all. hmm what is the possible code to use when I want to retrieve null values of access in listview (visual basic6) I try nz funtion but it doesnt work. Could anyone help me or teach me how to start to code. Thanks in advance for every input here.

RAAGHAVAN
RAAGHAVAN

Really a beautiful solution I could find here. I was having the problem of selecting the null values along with dates and I was getting blank reports. Now with the suggested solutions, I could solve the problem in a friction of second. Thanks a lot.

mje_1964
mje_1964

Thanks Tony- I figured it out. After thinking about what I wanted to do last night, I decided to format the field to have a background color automatically and then I used conditional formatting to take the background color away if it met the opposite criteria (ie: if it contained a date.) It gave me what I wanted, Thanks for your help.

mje_1964
mje_1964

Have a field with no value can I use IsNull to highlight that field & use conditional format

bill
bill

I have to compare two identical tables for indifferences but the NULLS are not being found. Does this logic make sense of should it be coded differently? WHERE ((([TABL_1]![TEMPB])[TABL_2]![TEMPB])) OR ((([TABL_1]![TEMPB]) Is Not Null) AND (([TABL_2]![TEMPB]) Is Null));

jkowolf
jkowolf

If you build a query in Access and put 'Is Not Null' in the criteria row under the field you want to exclude nulls, Access builds the SQL statement like this: SELECT Fee_Account FROM tbl_Account WHERE (((Fee_Account) Is Not Null)); Not saying the other way won't work, but this does and seems to make sense. I don't know if it proper or not.

Tony Hopkinson
Tony Hopkinson

Where not(source.field) is null does not make any sort of sense to me Where not source.field is null Or Where not (source.field is null) in proper SQL you might find a use for the NullIf function which is a sort of inverse of IsNull NullIf(Source.Field,0) useful if someone in the past got round a nullability problem by putting rubbish in the field.

JodyGilbert
JodyGilbert

Have you ever run into problems caused by someone who neglected to accommodate null values? Are there any additional situations where you've had to work around or proactively handle null values to prevent errors?

Tony Hopkinson
Tony Hopkinson

Ask in questions, and post a bit of the code. Oh and why are you still using VB6?

Tony Hopkinson
Tony Hopkinson

IsNull([SomeField],'Unknown') in a condition might as well just use ([SomeField] is null) If it's you want 'unknown' on the report and to do some condtional output, then by all means.

Tony Hopkinson
Tony Hopkinson

as null = null then it gets messy Where (NOT (f1 is null) and (f2 is null)) or ((f1 is null) and (f2 is not null)) or ((f1 is not null) and (f2 is null)) or ((f1 is not null) and (f2 is not null) and (f1 f2)) Another way is to use the isnull function, it's a bit naughty depends on how whatever uses the data treats nulls. for instance if nulls in an integer field are treated as zeros in the application then isNull(f1,0) isnull(f2,0) would do the job, if you want to say the NULL = 0 ! you could use another value instead of zero e.g. -1 given that - 1 should never be in the column. HtHs

Maevinn
Maevinn

That's not designed to be code, but rather the parens are used to indicate that a feild name is inserted in that location within the line of code. The line of code would read as either of your options--though the latter would likely make it cranky. Access generates SQL code, but it's not the cleanest line.

dawgit
dawgit

It's just that easy. Darn, now I'm going to have to do some more homework. And I thought I had explained that, to somebody, once. In the old days (even before M$ exclude) we solve that with 0.0 and with a '+' or '-' before a number. Later with a '$' for monitary sum. The rest of that solution is some where in my old memory bank poluted with a lot of coffee. It will come back to me though. Was this a test of some sort? Geeze I think I seen this some where else. B-)

Editor's Picks