#Error using Sum

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a character limit in the Expression dialog box when adding fields
together? I can get up to eight fields to total before I get the #Error?

I am trying to calculate subtotals and totals for a report. The report is
bound to the table.
I created 10 text boxes and set the controlsource(s) for each field that I
wanted subtotaled. This is working correctly. I created another text box in
the same group footer to total all 10 of the fields for a grand total but
cannot get the calc to work.

I get a #Error if use the =sum(
![field1]+
![field2]+etc....)

I have tried using a query with a calculated field but there are multiple
records and I only get the total for one record.

I have tried using the fields that have the subtotals but I still get the
#Error.
 
You could try summing each field and then adding them, rather than adding
the fields and then summing them:
=Sum(
.[field1]) + Sum(
.[field2]) + Sum(
.[field3]) +
....

Actually, there's a potential problem if any one returns a Null, so it would
be better to use:
=Nz(Sum(
.[field1]),0) + Nz(Sum(
.[field2]),0) + ...

Make certain that the Name of this calculated text box is not the same as
the name of any field. Access gets confused if the text box has the same
Name as a field, but is bound to something else.

Also double-check that this control is in a group footer or report footer,
not the page footer.

You should also be able to sum the values in a query, and then sum the total
in the report. In the Field row of your query you could need:
RowTotal: CCur(Nz(
.[field1],0) + Nz(
.[field1],0) + ...
and then in the Report Footer:
=Sum([RowTotal])

Regarding the typecasting of the calculated query field, see:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html
 
Back
Top