How-to: Null values

A NULL is a value that has not been entered, so can be thought of as unknown or missing.

In any database some data values will be required and some will be optional, the NULL allows you to recognise and handle these 'not entered' values.

Handling NULLs correctly is an important task in writing any database code, this is not limited just to Microsoft Access.

In VBA code it is useful to use the Nz() function to change the NULL value into a concrete value that you can test.

In SQL queries it is useful to use the SQL IS operator with Null to find Null values:


IS NULL query

You can’t use the = operator to compare Null values to anything, as the result will always be Null.

The IsNull() function can be used to test if a variable is NULL, but unlike Nz() it will not flag Zero Length Strings:

MyVar = Null
MyCheck = IsNull(MyVar)

“Empty pockets never held anyone back. Only empty heads and empty hearts can do that” ~ Norman Vincent Peale


Error trapping ODBC errors
Error trapping NULLs - You tried to assign the null value to a variable
Nz - Detect a NULL value or Zero Length string.

Copyright © 1999-2024
Some rights reserved