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

On Aug 21, 7:40*pm, BRC <brc1051-goog...@yahoo.com> wrote:
> 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.

On Aug 21, 8:40*pm, BRC <brc1051-goog...@yahoo.com> wrote:
> 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

On Aug 21, 9:55*pm, BRC <brc1051-goog...@yahoo.com> wrote:
> On Aug 21, 7:40*pm, BRC <brc1051-goog...@yahoo.com> wrote:
>
> > 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

On Aug 22, 6:59*am, "de...@westcreekltd.com" <dbels...@gmail.com>
wrote:
> On Aug 21, 8:40*pm, BRC <brc1051-goog...@yahoo.com> wrote:
>
> > 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