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 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