The concept of Null values confuses a lot of beginning Access users.

Null values can cause error messages and unexpected results in forms

and reports. One way to deal with Null values is by using the IsNull

function in the following form.

varTotal = IIf(IsNull(varAmount), 0, varAmount)

If varAmount is Null, the IIf function returns 0. Otherwise, the IIf

function returns varAmount. While this approach works well, the Nz

function can accomplish the same result, and it can save you a few

keystrokes to boot.

Nz determines whether a variant is Null. If it is, the function returns

a zero, a zero length string, or any other value you specify. To

illustrate, consider the expression (5 + varX), which returns a Null

value when the variant varX is Null. On the other hand, the expression

5+ Nz(varX) returns 5. If you don't specify a second argument, Nz

returns a value of zero when its first argument is Null.

If you want Nz to return a different value when the variant you're

testing is Null, simply add it to the function call, as shown below.

varTotal = Nz(varAmount, "Nothing paid")

In the statement, if varAmount is not Null, varTotal displays the value

of varAmount. Otherwise, varAmount shows the string "Nothing paid."


Created Date: 11/07/2001  Last Reviewed: 11/07/2001  Rev. Date: 

This site was last updated 06/11/06