Handling Null in Access
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:
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:
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:
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.
Copyright & Disclaimer
Last modified at 06/06/2006 14:58:06