Microsoft KB Archive/295619

= ACC2002: Nz Method of the Data Source Control Does Not Function as Expected =

Article ID: 295619

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q295619



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SYMPTOMS
The Nz method of the data source control is suppose to replace Null values with a zero (0) or some other specified value. However, the method does not replace Null values as expected.



RESOLUTION
Use a condition, similar to the following, to test for a value that is Null or a zero-length string: If Len(Fax.Value) = 0 Then ... End If



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



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 (&quot;&quot;), 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, &quot;No Freight Charge&quot;)

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 &quot;Resolution&quot; section of this article.

