Getting zeroes to display

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. 

Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.