Handling Null in Access

Handling Null in Access

Access has a special value, Null. This value indicates that the variable contains no valid data, or is unknown. The only variable that can legally contain a Null value is a variant (if you try to assign a Null value to any other datatype, then you will get an error, 94 - Invalid Use of Null).

As Null values are never known, and therefore can never equal anything, you cannot test directly for equality (i.e. If varNull=Null Then...), as this will always return False. Instead, you must use the IsNull function in VBA to check if a value is a Null:

    Dim varNull As Variant
    If IsNull(varNull) Then
'       The variant contains a Null value.
    Else
'       The variant does not contain a Null value.
    End If

You can quite easily convert a Null value to a zero-length string (ZLS or "") by concatenating it with a ZLS. This process will only convert a Null to a ZLS, and not affect the data variable in any other way:

Dim varNull As Variant
varNull=varNull & ""

Access in many cases has two sets of string functions, such as Left and Left$. The former returns a variant, and therefore accepts Nulls, whereas the latter returns a string. The string version is more efficient, but if there is the possibility of dealing with Nulls, then you should use the variant version.

Another pair of functions that differ in how they deal with Null values are those used for concatenation, '&' and '+'. The latter will allow Nulls to propagate:

Sub sConcat1()
    Dim var1 As Variant, var2 As Variant, var3 As Variant
    var1 = Null
    var2 = "Some Text"
    var3 = Null
    Debug.Print var1 + var2 + var3
End Sub

Sub sConcat2()
    Dim var1 As Variant, var2 As Variant, var3 As Variant
    var1 = Null
    var2 = "Some Text"
    var3 = Null
    Debug.Print var1 & var2 & var3
End Sub

The first procedure will output Null, whereas the second will print "Some Text". This can be useful in some cases, such as dealing with addresses.

Top

Last modified at 06/06/2006 14:58:06