Showing and Hiding Zeroes in Reports

  • Thread starter Thread starter Janet Friesen
  • Start date Start date
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
 
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?
Click to expand...

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.
 
Back
Top