Convert a sum in a report from a null value to zero

I created a report that utilized the sum function. however if there is
a result that is 0 is doesn't display 0, it shows as null (blank) how
can I get it to display the 0's in my report when there is no data that
was summed?

re: Convert a sum in a report from a null value to zero

"Cruisemate" wrote
[color=blue]
> I created a report that utilized the
> sum function. however if there is
> a result that is 0 is doesn't display 0,
> it shows as null (blank) how can I
> get it to display the 0's in my report
> when there is no data that was
> summed?[/color]

The NZ function converts a Null to Zero; if an actual zero result is
displaying as blank, you should use the Format property of the Control and
make certain it does not have an entry for 0 that shows blank.

Larry Linson
Microsoft Access MVP

re: Convert a sum in a report from a null value to zero

I"m not sure how to use the NZ function within the report. The format
property does not have an entry for 0 that shows blank.

re: Convert a sum in a report from a null value to zero

"Cruisemate" wrote
[color=blue]
> I"m not sure how to use the
> NZ function within the report.
> The format property does not
> have an entry for 0 that shows
> blank.[/color]

I could assume from your question that you have a Control on the Report with
a ControlSource of =Sum([SomeField]). It would be helpful if you'd include a
bit more detail with your question (but not enough to bury us in detail
<GRIN>).

If the values in [SomeField] are Null, then nothing will show. You could try
using as ControlSource the expression =
IIF(IsNull(Sum([SomeField]),"0",Sum([SomeField])) -- in a simple test case
where there were no "matching order detail records", thus giving a Null
summation, it worked for me.

Be sure to rename Controls so they do not have identical names to the
underlying Fields, if either are used in calculations.

Larry Linson
Microsoft Access MVP