Microsoft Access Tips for Casual Users

Provided by Allen Browne, November 1999.

Nulls: Do I need them?

Why have Nulls?

Learning to handle Nulls can be frustrating. Occasionally I hear newbies ask, "How can I prevent them?" Nulls are a very important part of your database, and it is essential that you learn to handle them.

A Null is "no entry" in a field. The alternative is to require an entry in every field of every record! You turn up at a hospital too badly hurt to give your birth date, and they won't let you in because the admissions database can't leave the field null? Since some fields must be optional, so you must learn to handle nulls.

Nulls are not a problem invented by Microsoft Access. They are a very important part of relational database theory and practice, part of any reasonable database. Ultimately you will come to see the Null as your friend.

Think of Null as meaning Unknown.

Null is not the same as zero

Open the Immediate Window (press Ctrl+G), and enter:

	? Null = 0

VBA responds, Null. In plain English, you asked VBA, Is an Unknown equal to Zero?, and VBA responded with, I don't know. Null is not the same as zero.

If an expression contains a Null, the result is often Null. Try:

	? 4 + Null

VBA responds with Null, i.e. The result is Unknown. The technical name for this domino effect is Null propagation.

Nulls are treated differently from zeros when you count or average a field. Picture a table with an Amount field and these values in its 3 records:

	4, 5, Null

In the Immediate window, enter:

	? DCount("Amount", "MyTable")

VBA responds with 2. Although there are three records, there are only two known values to report. Similarly, if you ask:

	? DAvg("Amount", "MyTable")

VBA responds with 4.5, not 3. Nulls are excluded from operations such as sum, count, and average.

Hint: To count all records, use Count("*") rather than Count("[SomeField]"). That way Access can respond with the record count rather than wasting time checking if there are nulls to exclude.

Null is not the same as a zero-length string

VBA uses quote marks that open and immediately close again to represent a string with nothing in it. If you have no middle name, it could be represented as a zero-length string. That is not the same as saying your middle name is unknown (Null). To demonstrate the difference, enter this into the Immediate window:

	? Len(""), Len(Null)

VBA responds that the length of the first string is zero, but the length of the unknown is unknown (Null).

Text fields in an Access table can contain a zero-length string to distinguish Unknown from Non-existent. By default the Allow Zero Length property is set to No, as the difference between a Null and a zero-length string is not very apparent to the user and the interface can be quite confusing. For data stored within an Access database, this property should rarely be changed to Yes.

Null is not the same as Nothing or Missing

These are terms that sound similar but mean do not mean the same as Null, the unknown value.

VBA uses Nothing to refer to an unassigned object, such as a recordset that has been declared but not set.

VBA uses Missing to refer to an optional parameter of a procedure.

To help you avoid common traps in handling nulls, see: Common Errors with Null