calculations

G

Guest

The reply makes perfect sense to me, but I STILL can't
get it to work. The problem apparently lies within my
subform. The calculation for each line item total (qty x
cost) works just fine. But when I try to sum all of the
line item total costs, I get the same #Error message as I
did in my main form. My expression is as follows:
=Sum([Total Price])
(Total Price being the name of the line item total field)
I have tried inputting this expression manually and with
the expression builder, with the same results either way.
I have ensured that my field property is set to currency
and that I am drawing the info from the correct form.

What am I missing?
-----Original Message-----
I'm having trouble building an expression that works.
Here's my scenario:
Purchase Order form
Purchase Order subform - separate line items for each
part ordered, cost, units ordered, total cost
Now I want to create, in the Purchase Order form, a
calculated field to add up all the line item total costs.
I tried to use this expression:
=Sum([frmPurchaseOrdersSub].Form![Total Cost])
It accepted the expression, but then it gives me #Error
in the box. I have set the format as currency, and my
line item totals work just fine.
What am I doing wrong?

Place an invisible summary control, say
txtSumofTotalCost,
in the subform footer with Control Source set to:

=Sum([Total Cost])

Refer to it in your main form control source:
=frmPurchaseOrdersSub.Form!txtSumofTotalCost

Kevin Sprinkel
Becker & Frondorf
..
 
C

chris

A possibility...
When you drop a field onto a report Access defaults the
control name the same as the field name, and then get
horribly confused when that name is used in formulas.

If this is the case, change the names of the controls eg
Field1 to txtField1
-----Original Message-----
The reply makes perfect sense to me, but I STILL can't
get it to work. The problem apparently lies within my
subform. The calculation for each line item total (qty x
cost) works just fine. But when I try to sum all of the
line item total costs, I get the same #Error message as I
did in my main form. My expression is as follows:
=Sum([Total Price])
(Total Price being the name of the line item total field)
I have tried inputting this expression manually and with
the expression builder, with the same results either way.
I have ensured that my field property is set to currency
and that I am drawing the info from the correct form.

What am I missing?
-----Original Message-----
I'm having trouble building an expression that works.
Here's my scenario:
Purchase Order form
Purchase Order subform - separate line items for each
part ordered, cost, units ordered, total cost
Now I want to create, in the Purchase Order form, a
calculated field to add up all the line item total costs.
I tried to use this expression:
=Sum([frmPurchaseOrdersSub].Form![Total Cost])
It accepted the expression, but then it gives me #Error
in the box. I have set the format as currency, and my
line item totals work just fine.
What am I doing wrong?

Place an invisible summary control, say
txtSumofTotalCost,
in the subform footer with Control Source set to:

=Sum([Total Cost])

Refer to it in your main form control source:
=frmPurchaseOrdersSub.Form!txtSumofTotalCost

Kevin Sprinkel
Becker & Frondorf
..


.
 
J

John Vinson

The reply makes perfect sense to me, but I STILL can't
get it to work. The problem apparently lies within my
subform. The calculation for each line item total (qty x
cost) works just fine. But when I try to sum all of the
line item total costs, I get the same #Error message as I
did in my main form. My expression is as follows:
=Sum([Total Price])
(Total Price being the name of the line item total field)
I have tried inputting this expression manually and with
the expression builder, with the same results either way.
I have ensured that my field property is set to currency
and that I am drawing the info from the correct form.

What am I missing?

Is [Total Price] the name of a textbox on the form, or of a field in
the Form's recordsource query? Only the latter can be summed!

If it's a textbox, go into the Form's recordsource query, and do the
row total there (using a bound textbox on the form to display it). The
sum should then work if you put it in the Form footer.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top