Provided by Allen Browne. Created November 1999. Updated March 2010
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.
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.
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. However, there is no difference visible to the user, so you are likely to confuse the user (as well as the typical Access developer.) Recent versions of Access default this property to Yes: we recommend you change this property for all Text and Memo fields. Details and code in Problem Properties.
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
| Home | Index of tips | Top |