Using "Avg" function on calculated field

[Guest]

In a report I have a calculated field as "=Nz([Total],0)/Hrs" which works
fine. In the report footer I tried to get the average of the values in this
column so I used "=Avg(Nz([Total],0)/Hrs) but when I run it it asks me for
the value of "Hrs". Neither "hrs" nor "total" are calculated fields and no
error is generated either. The output for the average is just blank. I tried
=Avg(Nz([Total],0)/(Nz(Hrs,))) which did not work either. What am I missing?

 

[Allen Browne]

There's a couple of potential issues here.

It sounds like Hrs is a field in the table/query the report is based on, but
do you have a text box for it on the report? If not, add one. Visible = No
if you like. Sometimes the report optimizer is just a bit too smart: if it
finds no control on the report bound to the field, it doesn't bother to
fetch the field, so expressions involving the field fail. The text box bound
to the field forces it to fetch the field.

Presumably Total and Hrs are fields of type Number (not Text.) That's
correct, but Access is not always clear about the data type once it comes
out of Nz(). You might try:
= Avg( CDbl( Nz( [Total], 0)) / [Hrs] )
and also set the Format property of the text box to Number.

The expression is a bit unusual. Normally the fields where Total is null do
not affect the average. Using Nz() forces those cases to zero, which will
lower the overall average. If that is not what you want, you can drop the
Nz().

Another option is to move the expression into the report's source query as a
calculated field:
TotalOnHrs: CDbl( Nz( [Total], 0)) / [Hrs]
so in the report footer you can simply:
=Avg([TotalOnHrs])

 

[Guest]

Thank You Allen
CDbl did not solve the problem. Some how Report could not find "Hrs" even
though it could find it in the detail section. (I do have a field name "Hrs"
in the report.) Then in the Avg function I changed the "Hrs" to its "control
Source" which is "manHrs" then it worked. Can you explain it to me that why
in the detail section it works with the field name but not in the Avg
function? My understanding is that field name should be used every where.

There's a couple of potential issues here.

It sounds like Hrs is a field in the table/query the report is based on, but
do you have a text box for it on the report? If not, add one. Visible = No
if you like. Sometimes the report optimizer is just a bit too smart: if it
finds no control on the report bound to the field, it doesn't bother to
fetch the field, so expressions involving the field fail. The text box bound
to the field forces it to fetch the field.

Presumably Total and Hrs are fields of type Number (not Text.) That's
correct, but Access is not always clear about the data type once it comes
out of Nz(). You might try:
= Avg( CDbl( Nz( [Total], 0)) / [Hrs] )
and also set the Format property of the text box to Number.

The expression is a bit unusual. Normally the fields where Total is null do
not affect the average. Using Nz() forces those cases to zero, which will
lower the overall average. If that is not what you want, you can drop the
Nz().

Another option is to move the expression into the report's source query as a
calculated field:
TotalOnHrs: CDbl( Nz( [Total], 0)) / [Hrs]
so in the report footer you can simply:
=Avg([TotalOnHrs])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

In a report I have a calculated field as "=Nz([Total],0)/Hrs" which works
fine. In the report footer I tried to get the average of the values in
this
column so I used "=Avg(Nz([Total],0)/Hrs) but when I run it it asks me for
the value of "Hrs". Neither "hrs" nor "total" are calculated fields and no
error is generated either. The output for the average is just blank. I
tried
=Avg(Nz([Total],0)/(Nz(Hrs,))) which did not work either. What am I
missing?

 

[Allen Browne]

In a reprort, you cannot sum a text box (nor an expression in a text box.)
You can sum a numeric field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank You Allen
CDbl did not solve the problem. Some how Report could not find "Hrs" even
though it could find it in the detail section. (I do have a field name
"Hrs"
in the report.) Then in the Avg function I changed the "Hrs" to its
"control
Source" which is "manHrs" then it worked. Can you explain it to me that
why
in the detail section it works with the field name but not in the Avg
function? My understanding is that field name should be used every where.

There's a couple of potential issues here.

It sounds like Hrs is a field in the table/query the report is based on,
but
do you have a text box for it on the report? If not, add one. Visible =
No
if you like. Sometimes the report optimizer is just a bit too smart: if
it
finds no control on the report bound to the field, it doesn't bother to
fetch the field, so expressions involving the field fail. The text box
bound
to the field forces it to fetch the field.

Presumably Total and Hrs are fields of type Number (not Text.) That's
correct, but Access is not always clear about the data type once it comes
out of Nz(). You might try:
= Avg( CDbl( Nz( [Total], 0)) / [Hrs] )
and also set the Format property of the text box to Number.

The expression is a bit unusual. Normally the fields where Total is null
do
not affect the average. Using Nz() forces those cases to zero, which will
lower the overall average. If that is not what you want, you can drop the
Nz().

Another option is to move the expression into the report's source query
as a
calculated field:
TotalOnHrs: CDbl( Nz( [Total], 0)) / [Hrs]
so in the report footer you can simply:
=Avg([TotalOnHrs])

In a report I have a calculated field as "=Nz([Total],0)/Hrs" which
works
fine. In the report footer I tried to get the average of the values in
this
column so I used "=Avg(Nz([Total],0)/Hrs) but when I run it it asks me
for
the value of "Hrs". Neither "hrs" nor "total" are calculated fields and
no
error is generated either. The output for the average is just blank. I
tried
=Avg(Nz([Total],0)/(Nz(Hrs,))) which did not work either. What am I
missing?