Averaging a column some have zero data

[Bobby1st]

Hello,
I have an existing table with data and I added a new column with a default to zero. When I query the table-column the results will show zero content for the previous dates and data for the current dates. I have a " =Avg([theColumn]) " on a textbox. The result in not correct, it counts the zero rows as part of the divisor. Any idea how to write the code?

Thanks

 

[JoeyY]

Have you tried using DAvg?

=DAvg("[theColumn]","tableorqueryname","[theColumn]>0")

 

[Bobby1st]

JoeyY,

I WORKS!, thank you. I have seen it but didn't have chance to apply to a situation, similar to seldom use SumIf. Wonderful!

Thanks,
Bobby1st

 

[Bobby1st]

JoeyY,

I thought it was working right but it was brought to my attention that, this function is giving blank result if there no data instead of zero. *** I solved this one: =iif(Avg([Flexible]=0,0,DAvg([Flexible],"qryYTD Rollup",[Flexible]>0)) ***

Second, my test report have 10 line item months, six zeroes for 2007 and 4 months with data (Jan-Apr 2008). The DAvg() is using divisor of 10 instead of 4.

For trend, the report details monthly data. At the bottom is a textbox where the y-t-d totals appears, mostly avg(). I can't use in the divisor the months the data is not yet collected.

Can you spot were I am getting it wrong with you expert eyes.

Thank you.

 

[Rich]

Just put >0 in the query criteria column for the field in question