Getting zeroes to display

Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.

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.