Nz

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

Syntax
      Nz(expression, [ valueifnull ])

Key
   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 may 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.

Examples

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

Related:

IIf - If-Then-Else function
If Then Else - If-Then-Else
IsNull - Test if an expression is NULL


© Copyright SS64.com 1999-2013
Some rights reserved