Microsoft Access Tips for Casual Users

Provided by Allen Browne, November 1999.

Common Errors with Null

Here are some common mistakes newbies make with Nulls. If you are unclear about Nulls, first read Nulls: Do I need them?.

Error 1: Nulls in Criteria

If you enter criteria under a field in a query, it returns only matching records. Nulls are excluded when you enter criteria.

For example, say you have a table of company names and addresses. You want two queries: one that gives you the local companies, and the other that gives you all the rest. In the Criteria row under the City field of the first query, you type:

	"Springfield"

and in the second query:

	Not "Springfield"

Wrong! Neither query includes the records where City is Null.

Solution

Specify Is Null. For the second query above to meet your design goal of "all the rest", the criteria needs to be:

	Is Null Or Not "Springfield"

Note: Data Definition Language (DDL) queries treat nulls differently. For example, the nulls are counted in this kind of query:
    ALTER TABLE Table1 ADD CONSTRAINT chk1 CHECK (99 < (SELECT Count(*) FROM Table2 WHERE Table2.State <> 'TX'));

Error 2: Nulls in expressions

Maths involving a Null usually results in Null. For example, newbies sometimes enter an expression such as this in the ControlSource property of a text box, to display the amount still payable:

	=[AmountDue] - [AmountPaid]

The trouble is that if nothing has been paid, AmountPaid is Null, and so this text box displays nothing at all.

Solution

Use the Nz() function to specify a value for Null:

	= Nz([AmountDue], 0) - Nz([AmountPaid], 0)

Error 3: Nulls in Foreign Keys

While Access blocks nulls in primary keys, it permits nulls in foreign keys. In most cases, you should explicitly block this possibility to prevent orphaned records.

For a typical Invoice table, the line items of the invoice are stored in an InvoiceDetail table, joined to the Invoice table by an InvoiceID. You create a relationship between Invoice.InvoiceID and InvoiceDetail.InvoiceID, with Referential Integrity enforced. It's not enough!

Unless you set the Required property of the InvoiceID field to Yes in the InvoiceDetail table, Access permits Nulls. Most often this happens when a user begins adding line items to the subform without first creating the invoice itself in the main form. Since these records don't match any record in the main form, these orphaned records are never displayed again. The user is convinced your program lost them, though they are still there in the table.

Solution

Always set the Required property of foreign key fields to Yes in table design view, unless you expressly want Nulls in the foreign key.

Error 4: Nulls and non-Variants

In Visual Basic, the only data type that can contain Null is the Variant. Whenever you assign the value of a field to a non-variant, you must consider the possibility that the field may be null. Can you see what could go wrong with this code in a form's module?

	Dim strName as String
	Dim lngID As Long
	strName = Me.MiddleName
	lngID = Me.ClientID

When the MiddleName field contains Null, the attempt to assign the Null to a string generates an error.

Similarly the assignment of the ClientID value to a numeric variable may cause an error. Even if ClientID is the primary key, the code is not safe: the primary key contains Null at a new record.

Solutions

(a) Use a Variant data type if you need to work with nulls.

(b) Use the Nz() function to specify a value to use for Null. For example:

	strName = Nz(Me.MiddleName, "")
	lngID = Nz(Me.ClientID, 0)

Error 5: Comparing something to Null

The expression:

	If [Surname] = Null Then

is a nonsense that will never be True. Even if the surname is Null, VBA thinks you asked:

	Does Unknown equal Unknown?

and always responds "How do I know whether your unknowns are equal?" This is Null propagation again: the result is neither True nor False, but Null.

Solution

Use the IsNull() function:

	If IsNull([Surname]) Then

Error 6: Forgetting Null is neither True nor False.

Do these two constructs do the same job?

(a)	If [Surname] = "Smith" Then
	    MsgBox "It's a Smith"
	Else
	    MsgBox "It's not a Smith"
	End If

(b)	If [Surname] <> "Smith" Then
	    MsgBox "It's not a Smith"
	Else
	    MsgBox "It's a Smith"
	End If

When the Surname is Null, these 2 pieces of code contradict each other. In both cases, the If fails, so the Else executes, resulting in contradictory messages.

Solutions

(a) Handle all three outcomes of a comparison - True, False, and Null:

	If [Surname] = "Smith" Then
	    MsgBox "It's a Smith"
	ElseIf [Surname] <> "Smith" Then
	    MsgBox "It's not a Smith"
	Else
	    MsgBox "We don't know if it's a Smith"
	End If

(b) In some cases, the Nz() function lets you to handle two cases together. For example, to treat a Null and a zero-length string in the same way:

	If Len(Nz([Surname],"")) = 0 Then