ACC2002: Nz Method of the Data Source Control Does Not Function as Expected (295619)

If Len(Fax.Value) = 0 Then
    ...
End If
				

MORE INFORMATION

The Help system for the Microsoft Script Editor defines the Nz method as follows:

Nz Method

Use this method to return zero, a zero-length string (""), or another specified value when a value 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. Returns a Variant.

expression.Nz(Value, ValueIfNull)
				

expression Required. An expression that returns a DataSourceControl object.

Value Required Variant. The value to convert.

ValueIfNull Optional Variant. Value to return if the Value argument is Null. This argument enables you to return a value other than zero or a zero-length string.

Remarks
This method 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 this method to return a 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 + MSODSC.Nz(varX) returns 2.

In the next example, the optional argument supplied to the Nz method provides a string to be returned if varFreight is Null.

varResult = MSODSC.Nz(varFreight, "No Freight Charge")
				

Current Functionality

Although the Nz Method is correctly documented in Microsoft Script Editor Help, it does not function as expected.

There are other methods that check for Null values as well. They are illustrated below. However, these methods cannot be used as valid workarounds to the Nz Method problem.

None of the following methods will evaluate to True, even when the Fax value is Null:

If Fax.Value = Null Then
    ...
End IF
				

If IsNull(Fax.Value) Then
    ...
End If
				

If IsEmpty(Fax.Value) Then
    ...
End If
				

For a valid workaround for the Nz Method, see the "Resolution" section of this article.