averaging a field value if field value is greater then 0 access report

[BRC]

I have a report that is grouped by date. I have a Textbox in the
heading that calculates the average of a field1 in the report. the
formula in the field is "=Round(Avg([field1]),2)". This works exactly
as it should but i would like to modify it so that it lnly calculates
for values greater then 0 but not sure where to plase the restriction
in the criteria.
any help would be greatly appreciated. Thanks in advance for any
suggestions

 

[BRC]

I have a report that is grouped by date.  I have a Textbox in the
heading that calculates the average of a field1 in the report.  the
formula in the field is "=Round(Avg([field1]),2)".  This works exactly
as it should but i would like to modify it so that it lnly calculates
for values greater then 0 but  not sure where to plase the restriction
in the criteria.
any help would be greatly appreciated.  Thanks in advance for any
suggestions

I should have included the info; this is a mdb file and I am using
access 2010. I have also tried the sytax
=Avg(IIf(field1 <> 0, field1, Null)) which looks like it should work
but when i type this into the control source I get "the expression you
have entered has invalid syntax" error message. thanks again for any
suggestions.

 

[derek]

I have a report that is grouped by date.  I have a Textbox in the
heading that calculates the average of a field1 in the report.  the
formula in the field is "=Round(Avg([field1]),2)".  This works exactly
as it should but i would like to modify it so that it lnly calculates
for values greater then 0 but  not sure where to plase the restriction
in the criteria.
any help would be greatly appreciated.  Thanks in advance for any
suggestions

what you need is to make 0 records NULL not 0

in computer math null means I don't know or care, 0 means I know and
there was none

You use average() when you want the average of some field that you
only check every 5 records ie the other 4 are null ie you didn't
check. average() ignores NULL

If you check a value every record and the fact that there is 0 items
means bad then the field is needs to default to 0 or you need to count
the records = N then sum the fileds then divide by N

if you use the built in Microsoft function it will ignore Null fields
ie
average (2,2,2,null,2) = 2
average (2,2,2,0,2) = 1.6
or
if you want to to get a true average you need to sum(fields) / count
(records) = 8/5 = 1.6.
Hope this helps

 

[derek]

I have a report that is grouped by date.  I have a Textbox in the
heading that calculates the average of a field1 in the report.  the
formula in the field is "=Round(Avg([field1]),2)".  This works exactly
as it should but i would like to modify it so that it lnly calculates
for values greater then 0 but  not sure where to plase the restriction
in the criteria.
any help would be greatly appreciated.  Thanks in advance for any
suggestions

I should have included the info; this is a mdb file and I am using
access 2010.  I have also tried the sytax
=Avg(IIf(field1 <> 0, field1, Null)) which looks like it should work
but when i type this into the control source I get "the expression you
have entered has invalid syntax" error message. thanks again for any
suggestions.

assuming you are using a query then add where is is not null to your
where class and forget the NZ
select round(average(field3)) as avg from table where field3 <> 0

field3 <> 0 if it might be negative

field3 > 0 will filter out NULL ,0 and negative if it not possible to
be negative. I know that null is less then 1

Maybe need field3 <> 0 and isnull(field) = false to be sure that it
is not null and not 0 but can be > 0 and <0

 

[BRC]

I have a report that is grouped by date.  I have a Textbox in the
heading that calculates the average of a field1 in the report.  the
formula in the field is "=Round(Avg([field1]),2)".  This works exactly
as it should but i would like to modify it so that it lnly calculates
for values greater then 0 but  not sure where to plase the restriction
in the criteria.
any help would be greatly appreciated.  Thanks in advance for any
suggestions

what you need is to make 0 records NULL not 0

in computer math null means I don't know or care,  0 means I know and
there was none

You use average() when you want the average of some field that you
only check every 5 records ie the other 4 are null ie you didn't
check. average() ignores NULL

If you check a value every record and the fact that there is 0 items
means bad then the field is needs to default to 0 or you need to count
the records = N then sum the fileds then divide by N

if you use the built in Microsoft function it will ignore Null fields
ie
average (2,2,2,null,2) = 2
average (2,2,2,0,2) = 1.6
or
 if you want to to get a true average you need to sum(fields) / count
(records) = 8/5 = 1.6.
 Hope this helps

Derek
Thanks for the response. I learned that my problem was being caused
by another issue but this will help me when i get the other issue
resolved. Thanks again BRC