Using AVG in a report

[bobh]

Hi All,
In an AccessXP report in a Group Footer I have the following in a
textbox and I don't think its working.
Question; Is AccessXP smart enough to know to first sum up all the
values of
[DaysToReferral] and count how many before it calculates the average
for only those records
that met the criteria? or is it just capturing the last value for
that grouping and calculating it.

=Avg(IIf([dtMonth]=Forms!frmMainM enu!bxComprMth And [dtYear]=Forms!
frmMainMenu!bxP riorYrDte And [CatType]="DRef",[DaysToReferral],0))

thanks
bobh.

[Marshall Barton]

bobh wrote:

>Hi All,
In an AccessXP report in a Group Footer I have the following in a
textbox and I don't think its working.
Question; Is AccessXP smart enough to know to first sum up all the
values of
[DaysToReferral] and count how many before it calculates the average
for only those records
that met the criteria? or is it just capturing the last value for
that grouping and calculating it.

=Avg(IIf([dtMonth]=Forms!frmMainM enu!bxComprMth And [dtYear]=Forms!
frmMainMenu!bx PriorYrDte And [CatType]="DRef",[DaysToReferral],0))

Is your problem because you are using 0 for records that
don't meet your criteria? Normally, you should use Null
instead of 0.

--
Marsh

[bobh]

It has made no difference whether I use '0' or 'null' or ""

and so the question still remains........ .... in this case is Access
doing the right calc or is it just calc on the last record read into
the grouping.
bobh.

On Jan 30, 12:37 pm, Marshall Barton <marshbar...@wo wway.comwrote:

bobhwrote:

Hi All,
In an AccessXP report in a Group Footer I have the following in a
textbox and I don't think its working.
Question; Is AccessXP smart enough to know to first sum up all the
values of
[DaysToReferral] and count how many before it calculates the average
for only those records
that met the criteria? or is it just capturing the last value for
that grouping and calculating it.

=Avg(IIf([dtMonth]=Forms!frmMainM enu!bxComprMth And [dtYear]=Forms!
frmMainMenu!bxP riorYrDte And [CatType]="DRef",[DaysToReferral],0))

Is your problem because you are using 0 for records that
don't meet your criteria? Normally, you should use Null
instead of 0.

--
Marsh

[Marshall Barton]

If you want it to count only the non-Null values, then you
need to use Null. If you want to count all the records,
then use 0. Do not use quotes around either the 0 the Null.

Using Null worked for my tests. Maybe the [DaysToReferral]
field does not contain the values you think it does??
Either that or your report has something going on that I am
not aware of.
--
Marsh
bobh wrote:

>It has made no difference whether I use '0' or 'null' or ""

and so the question still remains........ .... in this case is Access
doing the right calc or is it just calc on the last record read into
the grouping.
bobh.

On Jan 30, 12:37 pm, Marshall Barton <marshbar...@wo wway.comwrote:

>bobhwrote:

>Hi All,
In an AccessXP report in a Group Footer I have the following in a
textbox and I don't think its working.
Question; Is AccessXP smart enough to know to first sum up all the
values of
[DaysToReferral] and count how many before it calculates the average
for only those records
that met the criteria? or is it just capturing the last value for
that grouping and calculating it.

>=Avg(IIf([dtMonth]=Forms!frmMainM enu!bxComprMth And [dtYear]=Forms!
frmMainMenu!bx PriorYrDte And [CatType]="DRef",[DaysToReferral],0))

Is your problem because you are using 0 for records that
don't meet your criteria? Normally, you should use Null
instead of 0.