Detect a NULL value. Convert NULLs to a Zero-length string, or another value.

      Nz(expression, [ valueifnull ])

   valueifnull    A value to return if expression is NULL.

                  If value_if_null is not specified and the 
                  expression = NULL the default value
                  returned will be a zero-length string.

The Nz() function can be used in VBA or in an SQL query.

MS Access makes a distinction between an empty string "" and a NULL value. If you type something into a field and then delete it, you will be left with a zero length string, in contrast if nothing has ever been entered into a field it will be NULL.
Nz() will detect either of these.

The value_if_null can be set to return any string or number. If Nz() is returning #Error this is most often due to returning a Data Type thats incompatible with your variable's Data Type.

The Nz function is very useful for trapping errors caused by NULL/missing values, it is one of the most popular/heavily used Access functions.


strDemo = Nz(Me!txtDescription)
strDemo = Nz(Me!txtName, "Error")

intProduct = Nz(Me!cmbProductID, 0)
If intProduct = 0 Then Msgbox "A required value is missing!"

“Is the glass half full, or half empty?
It depends on whether you're pouring, or drinking” ~ Bill Cosby


IIf - If-Then-Else function
If Then Else - If-Then-Else
IsNull - Test if an expression is NULL
Error trapping - You tried to assign the null value to a variable

© Copyright 1999-2015
Some rights reserved