reports: sum and avg difficulties

[Michael C via AccessMonster.com]

Hello,

I don't know why, but I cannot get my sum and avg to work. The report
displays all products, their perspective grand weight numbers (txtbox is
called [Grand]), and the amount of product produced for the given dateframe
(txtbox is called [Produced]). That all works. I am trying to get an
Average of all weight numbers shown , and a Sum of all production totals
shown .
I break it up by production line, and put the following formulas in the line
footer:
=Avg([Grand]) and =Sum([Produced])

Should be pretty simple, but instead of results, I am currently getting the
following error:
"The Microsoft Jet database engine could not execute the SQL statement
because it contains a field that has an invalid data type".


Any idea whats wrong?

Perry

 

[Marshall Barton]

I don't know why, but I cannot get my sum and avg to work. The report
displays all products, their perspective grand weight numbers (txtbox is
called [Grand]), and the amount of product produced for the given dateframe
(txtbox is called [Produced]). That all works. I am trying to get an
Average of all weight numbers shown , and a Sum of all production totals
shown .
I break it up by production line, and put the following formulas in the line
footer:
=Avg([Grand]) and =Sum([Produced])

Should be pretty simple, but instead of results, I am currently getting the
following error:
"The Microsoft Jet database engine could not execute the SQL statement
because it contains a field that has an invalid data type".

The aggregate functions (Count, Sum, Avg, etc) can only
operate on fields in the report's record source table/query.
They are not aware of controls in the report.

Change the functions to use the field names instead of the
control names.

The error message actually implies that you may already be
using the field names but that the fields contain text
values instead of numeric values. If this is the case, I
will ned more details about the Grand and Produced fields
before I could suggest something to deal with the problem.

 

[Michael C via AccessMonster.com]

Found my error. In my table, the fields in question were listed as text, and
had to be changed to Numbers for the calculations to work on the report.
It's always the simple stuff that bites you. Thank you for the help.

Perry

I don't know why, but I cannot get my sum and avg to work. The report
displays all products, their perspective grand weight numbers (txtbox is

[quoted text clipped - 10 lines]

"The Microsoft Jet database engine could not execute the SQL statement
because it contains a field that has an invalid data type".

The aggregate functions (Count, Sum, Avg, etc) can only
operate on fields in the report's record source table/query.
They are not aware of controls in the report.

Change the functions to use the field names instead of the
control names.

The error message actually implies that you may already be
using the field names but that the fields contain text
values instead of numeric values. If this is the case, I
will ned more details about the Grand and Produced fields
before I could suggest something to deal with the problem.