Access Hacks

Hack #47. Don't Let Nulls Ruin Data Summaries

When nulls are mixed in with valid data, incorrect results can occur. Here are some guidelines to tame the beast.

When you are dealing with values in Access, you might be tempted to think that a blank field is simply a blank field. However, there is a difference between a blank field that is filled in with an empty string and a blank field that is null. For example, when you are looking at number fields, there is a difference between a field with a 0 value and a field with a null value. This hack helps you work with these nonvalue values.

The first frustrating thing about nulls is that if you write a line such as this, every line will show up as Not Blank, even if you have null values:

	IIF([Amount]=Null,"Blank","Not Blank")

This occurs because in a Boolean expression, any item compared to Null returns False.

There is an easy way to deal with this, using a function available in Access called ISNULL. This function returns a Boolean and allows you to perform your test. Here is how to rewrite the previous example:

	IIF(ISNULL([Amount],"Blank","Not Blank")

That clinches it. Now, any encountered null is converted to Blank.

Nulls in Number Fields

Let's assume you have a table with a field called Amount. You are trying to determine the average of that field (assume also that the average doesn't need to be weighted). If you write a query that attempts to determine the average value, the SQL might look like this:

 SELECT Avg(tbl_Amount.Amount) AS AvgOfAmount ...