How to get calculated fields to display zero.
Q: Rob Stoker writes: I have a calculated field which is sum([nettprice]*[quantity]) which works ok. However if there is no data then it displays #Error. I have tried the Nz function, and tried the IIf function but I can’t seem to stop it doing it. I would like it to display 0.
A: Sometimes it can be quite difficult to get a calculated field to display zero. I would definitely use Nz() around the fields, to avoid errors, like this:
Sum(Nz([netprice]) * Nz([quantity]))
If the field still doesn’t display a zero, try wrapping in CCur():
CCur(Sum(Nz([netprice]) * Nz([quantity])))
That will usually do it.
