Can only sum one column on form

[Stuart Grant]

I have a form with data displayed in a single row on a continuous form.
There are 10 columns, 7 of them numeric.

I want to have totals for 5 columns. I put a textbox in the Footer under
the first column with the Control Source "=SUM([Cost])". Worked fine.
Correct total. I added the next textbox in the Footer under the second
column with the Control Source as =SUM([CurrentValue]) but then I
got"#Error" in both text boxes. Adding more text boxes produced the same
result. Took me ages to work out that only one such textbox works. Why ?

Stuart

 

[Allen Browne]

Hi Stuart. Sum() works vertically, not horizontally.
You need to call the fields by name, e.g.:
=[Cost] + [Fee] + [Tax]

If any one of those is null, the total will be null, so use Nz() around each
one:
=Nz([Cost],0) + Nz([Fee],0) + Nz([Tax],0)

 

[Stuart Grant]

Thanks Allen but I don't think I have explained well enough. The data in
the fields is all in vertical columns.
My first text box with source =SUM([Cost]) works fine. It gives the total
of data in the Cost column. I now want to add all the data in the Current
column. I don't want to add [Cost]+[Current]. All vertical sums. I want
SUM([Current]) but all I get is the error message and my SUM([Cost]) changes
to the same error message.

Stuart

 

[Allen Browne]

Just as you used:
=Sum([Cost])
to sum the Cost column, you can use:
=Sum([Current])
to sum the Current column, provided Current is a numeric field in the form's
source table/query.

Make sure this text box does not have a name that is the same as a field,
e.g. it must not be called Cost or Current. Access gets confused if the Name
is the same as a field, but it is bound to something else.

If Current is a Calculated control with a source such as:
=[Quantity] * [UnitPrice]
then you cannnot sum it directly. Instead repeat the expression inside
Sum(), e.g.:
=Sum([Quantity] * [UnitPrice])

If Current is a Text field in a table, trying to sum it will give errors. If
it is a calculated field in a query, you may need to typecast it, as
explained here:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Once Access finds a field it is unable to calculate, it gives up on the
others too, so that would be why your other column could go #Error as well.

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

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

Thanks Allen but I don't think I have explained well enough. The data in
the fields is all in vertical columns.
My first text box with source =SUM([Cost]) works fine. It gives the
total of data in the Cost column. I now want to add all the data in the
Current column. I don't want to add [Cost]+[Current]. All vertical
sums. I want SUM([Current]) but all I get is the error message and my
SUM([Cost]) changes to the same error message.

Stuart

Hi Stuart. Sum() works vertically, not horizontally.
You need to call the fields by name, e.g.:
=[Cost] + [Fee] + [Tax]

If any one of those is null, the total will be null, so use Nz() around
each one:
=Nz([Cost],0) + Nz([Fee],0) + Nz([Tax],0)

 

[Stuart Grant]

Right on !
The problem was a calculated field - very complicated calculation with two
multiplications and two divisions.
When I subsituted the calculation formula instead of SUM([Current]), it
worked perfectly. All the other totals work too.

Thank you very, very much.
Stuart