Sum in form footer

[Question Boy]

I have a continuous form to process orders

Qty/Unit Price/SubTotal/Taxes/Total

(the SubTotal/Taxes/Total control are all calculated fields)

I am trying to add a control with the cumulative sums for the
SubTotal/Taxes/Total, but keep getting #error? What is the common work
around?

QB

 

[Ken Snell \(MVP\)]

You cannot reference the name of a control in the Sum expression; you must
repeat in the Sum expression the actual expression from the control that you
want to sum. For example, if a control named Qty contains a calculated
expression such as this:

=[FieldName]*4

And you then want to sum the values from Qty control, you'd use this
expession in the SubQty control:

=Sum([FieldName]*4)

 

[Question Boy]

I've tried what you'd suggested but still get #error

My SubTotal control has the following exprssion as its control source
=Qty.Value*UnitPrice.Value

So then in the form footer I placed
=Sum(Qty.Value*UnitPrice.Value)

which access converts to
=Sum([Qty].[Value]*[UnitPrice].[Value])

This doesn't work so I changed it to
=Sum([Qty]*[UnitPrice])

but even this does not work. Any other suggestions? I must say I am very
surprised at the complexity of such a useful functionality! It can be done
in reports so easily, why the headache in forms?

Is there a vba way to loop through the forms records and do the sum that way?

QB

You cannot reference the name of a control in the Sum expression; you must
repeat in the Sum expression the actual expression from the control that you
want to sum. For example, if a control named Qty contains a calculated
expression such as this:

=[FieldName]*4

And you then want to sum the values from Qty control, you'd use this
expession in the SubQty control:

=Sum([FieldName]*4)

--

Ken Snell
<MS ACCESS MVP>

I have a continuous form to process orders

Qty/Unit Price/SubTotal/Taxes/Total

(the SubTotal/Taxes/Total control are all calculated fields)

I am trying to add a control with the cumulative sums for the
SubTotal/Taxes/Total, but keep getting #error? What is the common work
around?

QB

 

[Ken Snell \(MVP\)]

In which part of your form is the SubTotal control located? Do you have
controls that are bound to the Qty and UnitPrice fields in the Detail
section of the form? Do you have controls that are named Qty and UnitPrice
on the form (perhaps the controls that are bound to the fields with those
names)?

Somehow, the design of your form / recordsource is not letting ACCESS "find"
the fields and their values so that the Sum expression can be calculated.
--

Ken Snell
<MS ACCESS MVP>

I've tried what you'd suggested but still get #error

My SubTotal control has the following exprssion as its control source
=Qty.Value*UnitPrice.Value

So then in the form footer I placed
=Sum(Qty.Value*UnitPrice.Value)

which access converts to
=Sum([Qty].[Value]*[UnitPrice].[Value])

This doesn't work so I changed it to
=Sum([Qty]*[UnitPrice])

but even this does not work. Any other suggestions? I must say I am very
surprised at the complexity of such a useful functionality! It can be
done
in reports so easily, why the headache in forms?

Is there a vba way to loop through the forms records and do the sum that
way?

QB

You cannot reference the name of a control in the Sum expression; you
must
repeat in the Sum expression the actual expression from the control that
you
want to sum. For example, if a control named Qty contains a calculated
expression such as this:

=[FieldName]*4

And you then want to sum the values from Qty control, you'd use this
expession in the SubQty control:

=Sum([FieldName]*4)

--

Ken Snell
<MS ACCESS MVP>

I have a continuous form to process orders

Qty/Unit Price/SubTotal/Taxes/Total

(the SubTotal/Taxes/Total control are all calculated fields)

I am trying to add a control with the cumulative sums for the
SubTotal/Taxes/Total, but keep getting #error? What is the common work
around?

QB

 

[Phil S]

I wonder if this ever got resolved? I'm having the exact same problem.

I'm no Access novice, I've built many databases but this if my first time using Access 2007. I've got a simple continuous form. One text box control, called [txtQuantity] is not a calculated field, its control source is simply set to the field in the table, which is called 'Quantity'.

I simply want to have a total of [txtQuantity] in the form footer so I've created a text box in the footer and set the control source to '=Sum([txtQuantity])'. And it just produces '#Error'.

This is absolute basics but nothing I do will work. I've tried entering the full path to the field in the formula. I've tried changing the form's control source to a query. I've tried EVERYTHING!!

The only small clue I have is that if I recreate the form from scratch using the form wizard the corresponding control is called [Quantity] and not [txtQuantity]. If I create the same total control in the new form footer and set the control source to '=Sum([Quantity])' it seems to work. But as soon as I then add the txt prefix in the control name and the formula in the control source of the total control it breaks again. If I subsequently change this back, removing the txt prefix in both places it still doesn't work.

Surely this is some kind of bug in Access?? HELP!

 

[Dirk Goldgar]

I wonder if this ever got resolved? I'm having the exact same problem.

I'm no Access novice, I've built many databases but this if my first time
using Access 2007. I've got a simple continuous form. One text box
control, called [txtQuantity] is not a calculated field, its control
source is simply set to the field in the table, which is called
'Quantity'.

I simply want to have a total of [txtQuantity] in the form footer so I've
created a text box in the footer and set the control source to
'=Sum([txtQuantity])'. And it just produces '#Error'.

This is absolute basics but nothing I do will work. I've tried entering
the full path to the field in the formula. I've tried changing the form's
control source to a query. I've tried EVERYTHING!!

The only small clue I have is that if I recreate the form from scratch
using the form wizard the corresponding control is called [Quantity] and
not [txtQuantity]. If I create the same total control in the new form
footer and set the control source to '=Sum([Quantity])' it seems to work.
But as soon as I then add the txt prefix in the control name and the
formula in the control source of the total control it breaks again. If I
subsequently change this back, removing the txt prefix in both places it
still doesn't work.

Surely this is some kind of bug in Access?? HELP!

This is not a bug. The SQL Aggregate functions (Sum, Count, Min, Max, Avg,
etc.) only work on fields, not controls. Your text box may have been named
"txtQuantity", but the field to which it was bound is named "Quantity". On
your original form, just make the ControlSource of the totalling text box:

=Sum([Quantity])

Then it should work.

 

[boblarson]

In addition to what Dirk said, you also need to account for nulls and if
there are any then it can also cause your aggregate to fail. So, you would
use

=Sum(Nz([Quantity],0))
--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[Dirk Goldgar]

In addition to what Dirk said, you also need to account for nulls and if
there are any then it can also cause your aggregate to fail. So, you
would
use

=Sum(Nz([Quantity],0))

I think you're mistaken about that, Bob. The Sum function ought to ignore
any Null values and sum only the non-null ones.

 

[boblarson]

And I still disagree with you on this Dirk as I've typically gotten #Error
messages when nulls exist while using aggregates.
--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

In addition to what Dirk said, you also need to account for nulls and if
there are any then it can also cause your aggregate to fail. So, you
would
use

=Sum(Nz([Quantity],0))

I think you're mistaken about that, Bob. The Sum function ought to ignore
any Null values and sum only the non-null ones.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

And I still disagree with you on this Dirk as I've typically gotten #Error
messages when nulls exist while using aggregates.

Can you give me an example? Just to make sure, I created a simple test
form, and Nulls were ignored as I expected. So I'm guessing there's some
other factor at work in those cases.