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

 

[Pat Hartman]

I'm not clear as to whether you want zeros to print as blanks, or the cooresponding detail to not print at all.

If you want to "print" blank, Richie's suggestion will work as will setting the control's visible property to false. If you don't want the detail included at all, you should eliminate it in the query:
Select YourField, ...
From YourTable
Where YourField <> 0;

If you want the items with zeros to be included in calculations an average or count perhaps, you need to leave them as zero in the input recordsource and just suppress printing by some method. If you do not want them to be included in calculations but you do want to see the associated fields, then the amount needs to be null in the recordsource so you will need to change the query to:
Select IIf(YourField = 0,null,YourField) AS modYourField,....

 

[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