sum returns 0 when included fields with no value

[dudess]

i have a query that sums the values of various number
fields. when i try to create a sum field in the query, i
get no results unless all fields contain some number,
even if it is a 0. i do not want the field to have a
zero value for when it is used elsewhere. (on a report).
so my ? is, is there any way to get a field to have a
zero value, but not show it in a report? or else some
way to get the sum feature of the query to include blank
fields?

any help would be appreciated.

 

[Jeff Boyce]

If you are doing calculations on a field with a Null, that null
"propagates". This is by design, ... after all, if you have a "no value" as
part of your calculation, you probably shouldn't complete the calculation.

One solution, if appropriate mathematically, is to convert those nulls to
zero, using the Nz() function (that is, "Null to Zero"). An example would
look something like (actual syntax may vary):

Nz([YourField1], 0) + Nz([YourField2], 0) + ... Nz([YourFieldn], 0)

This will return "0" if ALL fields are nulls. And if you have any value in
a field, Nz() returns that value.

 

[notdave]

So you know you have to have a zero to do the SUM, but now
you have a zero you don't want to see. Use an IIf
statement to hide the zero, either in the query or on the
report.

IIf(thistrue,dothis,elsedothis)

You could do it in the query

if your expression is
Column1: SUM([amount])

try changing it to
Column1: IIf(SUM([amount])=0,Null,SUM([amount]))

this returns a Null if SUM = 0

or do it in the report

if your control source is
SumOfColumn1

try changing it to this
=IIf([SumOfColumn1]=0,Null,[SumOfColum1])

hope this helps...
~notdave