Can you ignore zeros when Summing?

[AUdby]

This might be no-brainer for some, but not me. When I created my report I was able to set it to sum certain columns, and that's just peachy, but if I have a column with no value then it automatically puts in zero (the data type is Currency, so it actually puts in $0). You can imagine the problems this causes with a small number of entries. Is there some way, either in the input form, the underlying table, or the query that feeds the report, to allow a field with a zero in it to be treated as a <Null>?

Thanks,
Andreas

 

[Richie]

cheat by changing the fore colour to white if amount=0

 

[AUdby]

Thanks for your replies, folks.

I really don't care if the zeros print or not, just as long as they're not included in the Avg calculations. I'll give your IIF suggestiona atry, Pat, since that's something I was playing with anyway. Thanks again!

Andreas

 

[AUdby]

Woohoo! Solved it! If anyone cares, here's how. My original 'average' control was coded thusly:

=Avg(agedSimpleMean)

My new control is now coded:
=IIf([simpleMeanCounter]=0,0,(Sum([agedSimpleMean]))/(Sum([simpleMeanCounter])))

where simpleMeanCounter is a field in the query that's populated by the following expression:

=IIf([agedSimpleMean]=0,0,1)

This way, the query itself tracks whether a field is carrying a zero or not.
It's ugly, but it works!

Andreas