Checking for Nulls

Another common area of confusion is checking for Null. This statement is not correct:

If varCustomerKey = Null Then

'even more interesting code here End If

An interesting fact about Null: It never equals anything. It is, by definition, unknown and undefined. A variable containing a null can't "equal" anything, including Null. In the above example, the interesting code will never run, no matter how null the customer key field is.

In order to check for a Null in a field, you must use the IsNull function, like this:

If IsNull(varCustomerKey) Then

'even more interesting code here End If

The IsNull function is the only way to look into a variable or recordset field and determine if there's a null in there. The "=" just can't do it. By the way, this is true in Access SQL too—you need to use IsNull to test for Nulls in the Where clauses of queries and recordsets.

0 0

Post a comment