Application.Nz (Access)

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.

If the Value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression), depending on whether the context indicates that the Value should be a number or a string. If the optional ValueIfNull argument is included, the Nz function will return the value specified by that argument if the variant argument is Null. When used in a query expression, the Nz function should always include the ValueIfNull argument. If the Value of Variant isn't Null, the Nz function returns the Value of Variant. The Nz function is useful for expressions that may include Null values. To force an expression to evaluate to a non- Null value even when it contains a Null value, use the Nz function to return zero, a zero-length string, or a custom return value. For example, the expression 2 + varX will always return a Null value when the Variant varX is Null. However, 2 + Nz(varX) returns 2. You can often use the Nz function as an alternative to the IIf function. For example, in the following code, two expressions including the IIf function are necessary to return the desired result. The first expression including the IIf function is used to check the value of a variable and convert it to zero if it is Null.

Nz (Value, ValueIfNull)

varTemp = IIf(IsNull(varFreight), 0, varFreight) 
varResult = IIf(varTemp > 50, "High", "Low")

Arguments

The following argument is required

Value (Variant) - A variable of data type Variant.

Optional arguments

The following argument is optional

ValueIfNull (Long) - 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. NOTE: If you use the Nz function in an expression in a query without using the ValueIfNull argument, the results will be a zero-length string in the fields that contain null values.