Missing values can confuse users if handled incorrectly. For instance, a calculated control in a form or report has the potential to return #Error if a value is missing. That’s going to leave your users scratching their heads and reaching for the phone to ask what’s up. You can avoid their confusion (and your interruption) by using the Nz() function to force a value — usually 0. This function’s syntax is simple:
Nz(expression, replacementvalue)where expression is the field or value being evaluated and replacementvalue is the value or text you want to display, or pass, when there’s no value.
(You can learn more about Nz() in Help.)
I’ve found that applying Nz() correctly can be a bit of a puzzle for some folks. The key is to wrap every field or value in an Nz() function, not the entire expression. For instance, the following expression has the potential to return #Error, despite the Nz() function:
=Nz(Sum(Price * Quantity, 0))The correct syntax follows:
=Sum(Nz(Price, 0) * Nz(Quantity, 0))Wrap each field within the expression to catch each missing value before the SUM() function tries to evaluate it.
Of course, the best solution is to prevent missing values in the first place. To do so at the table level, set the field’s Required property to Yes. However, doing so isn’t always appropriate or practical. That’s when the Nz() function comes in handy — as long as you apply it correctly.
