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)

“Measuring programming progress by lines of code is like measuring aircraft building progress by weight” ~ Bill Gates


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-2023
Some rights reserved