DEAL WITH NULL VALUES
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