Microsoft Access VBA Tip: Use the NZ() NullToZero Function to Handle Null Values
Behavior of Null Values in Equations (Expressions)
Any arithmetic calculation in VBA with a Null value results
in a null value. If one of the values is NULL, the result is Null (blank).
According to VBA rules for calculating values, the result is null because a
null value is an unknown.
Nulls are not the same value as zero (0). However, in many situations that's exactly what we want. We
want to treat Nulls as zeros.
Thankfully, Microsoft Access offers a way around this issue in VBA module code or queries. The NullToZero function (NZ) takes a null
value and converts it to zero. You should use this function anywhere a value
could be null (like a field that is not required).
Example for Using the NZ Function
The following can be used to calculate the sum of two fields
in the query:
Nz([Field1]) + Nz([Field2])
If [Field1] is null and [Field2] is 2, the result is 2.
Without the Nz() function, the result would be null. So be safe! Wrap your
potential null values with NZ.
Syntax for NZ Function
There are two parameters. The second one
is optional and lets you override the
default value for
nulls. For instance, you may want Nulls to be treated as 1, or for text
fields, a zero-length string ("") rather than zero.
Required or Optional
Value to evaluate for Null
The custom return value if the value being
evaluated is Null (overrides the default
- If the Value parameter is not null, the Value is
- If the Value parameter is null:
- IIf the ValueIfNull parameter is provided, ValueIfNull
- If the ValueIfNull parameter is not provided, a
zero (0) or zero-length string ("").
Note that when used in a query expression, the NZ
function returns a zero-length string.
Check out the VBA help file for the complete definition of the NZ
Additional Resource and Limitations of NZ Function
On a related note, read our whitepaper
Microsoft Access Query Sorting on Multiple Date or
Numeric Fields with Blank (Null) Values where we discuss how the Immediate
If command (IIF) is used to choose which field to sort and behaves differently
from using the NZ function.
Note that the Nz Function is a Microsoft Access VBA function and does
not exist in Visual Basic 6 (VB6) or other Office VBA