Description
You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.
Syntax Nz(variant[, valueifnull]) The Nz function has the following arguments.Argument | Description |
variant | A variable of data type Variant. |
valueifnull | Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string. If you use the Nz function in an expression in a query without using the valueifnull argument, the results will be empty in the fields that contain null values |
varTemp = IIf(IsNull(varFreight), 0, varFreight)
varResult = IIf(varTemp > 50, "High", "Low")
In the next example, the Nz function provides the same functionality as the first expression, and the desired result is achieved in one step rather than two.
varResult = IIf(Nz(varFreight) > 50, "High", "Low")
If you supply a value for the optional argument valueifnull, that value will be returned when variant is Null. By including this optional argument, you may be able to avoid the use of an expression containing the IIf function. For example, the following expression uses the IIf function to return a string if the value of varFreight is Null.
varResult = IIf(IsNull(varFreight), "No Freight Charge", varFreight)
In the next example, the optional argument supplied to the Nz function provides the string to be returned if varFreight is Null.
varResult = Nz(varFreight, "No Freight Charge")
See Also
Is operator, IsEmpty function, IsNull function.
Example
The following example evaluates a control on a form and returns one of two strings based on the control's value. If the value of the control is Null, the procedure uses the Nz function to convert a Null value to a zero-length string.
Sub CheckValue()
Dim frm As Form, ctl As Control
Dim varResult As Variant
' Return Form object variable pointing to Orders form.
Set frm = Forms!Orders
' Return Control object variable pointing to ShipRegion.
Set ctl = frm!ShipRegion
' Choose result based on value of control.
varResult = IIf(Nz(ctl.Value) = "", "No value", "Value is " & ctl.Value)
' Display result.
MsgBox varResult
End Sub