Getting zeroes to display

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Give it a try. You can unsubscribe at any time.

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.