Showing and Hiding Zeroes in Reports

J

Janet Friesen

Hello,

I have a problem in my reports. Sometimes I need to show
a zero because my calculation includes a field which is
sometimes blank. How do I get that field to show a zero
if it is blank, so that my calculation works?

Also, how do I show blanks instead of zeroes in a report?

Thanks!
Janet Friesen
 
M

Marshall Barton

Janet said:
I have a problem in my reports. Sometimes I need to show
a zero because my calculation includes a field which is
sometimes blank. How do I get that field to show a zero
if it is blank, so that my calculation works?

Also, how do I show blanks instead of zeroes in a report?

If you're not using the zero or blank (actually should be
Null) values in any calculations, then just use a custom
format on the control that displays the value.

A format can have four parts, one each for positive,
negative, zero and Null values. So, for example, you could
set a text box's Format property to something like this:

0.00;"Negative "0.00;"ZERO";"Not Specified"

In your case, you can show a blank instead of a zero by
using "" in the third part or you could display a 0 instead
of nothing by using "0" in the fourth part.

If you want to use zero (or anything else) for Null values
in a calculation, then you can use the Nz function. For
example, a text box might have a control source expression
like =txtboxA + Nz(txtboxB, 0) so when txtboxB is Null a
zero is added to the value in txtboxA. Watch out for this
when doing aggregate function (Count, Sum, Avg, etc), they
specifically ignore Nulls, which will affect the result.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top