Microsoft Office Tutorials and References
In Depth Information
Going beyond Basic Arithmetic
Figure 2-6:
Monthly
payment
calculations
shown in
Currency
format.
Avoiding problems with null values
Sometimes, a field in a record may be empty because nobody ever typed any
information in that field. The official name used to describe the value of an
empty field is null. If a field contains nothing, we say that it contains a null value.
Mathematical calculations don’t automatically treat a null value as being
the same thing as zero. If any field that’s used in a calculated field contains
a null, the expression itself also returns null. In Figure 2-7, the SubTotal
calculated field multiplies the contents of the HowMany field by the Price
field. In the query results, shown at the bottom of Figure 2-7, any field that
has a null in the HowMany or Price field ends up with a null value in the
SubTotal field as well.
Use the Nz() function to convert a null to a zero. What Nz() really means
is this: “If this field contains a null, make that into a zero, and do the math
using that zero.” To use the Nz() function, put the entire field name within
the function’s parentheses. In Figure 2-8, the modified calculated field uses
the following expression:
SubTotal: Nz( [HowMany], 0 * Nz( [Price], 0 )
In the Datasheet view of that same query, shown at the bottom of Figure
2-8, records that contain a null HowMany or Price field yield a zero result,
rather than null, in the SubTotal field. That’s because the modified
calculated control tells Access to use a zero rather than nothing (a null) to do the
math when a field is null.
Search JabSto ::

Custom Search