Nz in access

[Guest]

I am attempting to convert an Access application to a Visual Basic frontend,
and having fairly good luck so far. I have written routines to handle all the
Domain Aggregate Functions (DLookup and the like), but I would like to know
exactly what Nz does. From what I have been able to figure out, I think it
converts a null returned from say DSum to a zero value, but I need to know
that for sure before I can write a routine in Visual Basic. Someone please
help.

 

[Albert D. Kallal]

NZ simply lets you substitute a value when he control, or variable is null.


So,

debug.print nz(null,"this is null")

Often, we use nz in case the value, or control, or table value *can* have a
null value. Since nulls tend to cascade through expressions, then a null can
mess things up.

curMyResults = rst!InvoiceAmount + 100

The above would normally give you a result of the invoice amount + 100.
However, if InvoiceAmount can be null, then the above will throw up an
error. However, if curMyResults is of a type Variant, then you actually get
a null value stored!

So, for the above, we could use:

curMyREsults = nz(rst!InvoiceAmount,0) + 100

So, the above expression would return 0 for the null field. Of course,the
above is not the best example, since most people make number fields have a
default of 0, and make them required (that way, you never get null values
for numbers stored in your tables). However, I do make all "empty" string
fields NOT allow zero length, and thus I can assume that ANY and ALL empty
text/string field are null.

Anyway, you can create your own function in vb that would duplicate the NZ
function


Public Function myNZ(v As Variant, vSubValue As Variant) As Variant

If IsNull(v) = True Then
myNZ = vSubValue
Else
myNZ = v
End If

End Function

 

[Allen Browne]

Eqiuvalent to:

Public Function Nz(varInput As Variant, _
Optional varValueIfNull As Variant = Empty) As Variant
If Not IsNull(varInput) Then
Nz = varValueIfNull
Else
Nz = varInput
End If
End Function