Total a subform 1

[nberryman]

Am I losing it?

All I want to do is have a total for a field [SubTotal] in a sub form which calculates [Qty]* [Cost]

I have put =Sum([SubTotal]) in the footer of my subform but just get #error

Heeeeeeeelp please

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[docmeizie]

Does the Subtotal control calculate right when the Qty and Cost controls are entered???

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmm

 

[nberryman]

Yes they do, they work all the way down, it's just the Sum that fails



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[nberryman]

It's also failing when I try to Sum an unbound textbox on a normal form as well



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[docmeizie]

Hmmmmmm. Perplexing. Let's see, as long as the control source is set to =Sum([Subtotal]) it should work fine. Ahhhh, here we go. If you have any null values in any of the fields or subtotals the you want to use the nz() function to turn those to zeros. You may want to put this around the Subtotal control as so:

nz([Subtotal])

Also you may want to put it in the Sum control as so:

=sum(nz([Subtotal]))

Try that and see how it works...

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmm

 

[nberryman]

Tried that but still get #Error

This is very strange.........



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[docmeizie]

Wow, Have you tried this:

=Sum([Qty]*[Cost])

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmm

 

[nberryman]

Yahoooooooo, that works.

Better fill in the hole where I was battering my head now.

Many thanks



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[docmeizie]

LMAO. No Problem. I was having the same problem on some forms but on others it would work fine. So I have those three methods all working in one database:

=Sum([Subtotal])
=Sum(nz([Subtotal]))
=Sum([Qty]*[Cost])

WBT = Weird But True

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmm

 

[nberryman]

Just FYI from Bill Gates

ACC2000: You Cannot Sum Calculated Controls in Forms or Reports

--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft Access 2000

--------------------------------------------------------------------------------
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).



SYMPTOMS
When you open a form or a report, you may see the following behavior:

#Error or #Name? is displayed in a text box in the form footer.


A parameter box prompts you for the field that is specified in a sum expression within a report.





CAUSE
You are trying to have the text box sum a calculated control in the form or the report. Because Microsoft Access does not store calculated values, Access cannot sum the calculated field.



RESOLUTION
To work around this behavior, use one of the following two methods . You can use Method #1 only with .mdb files. You can use Method #2 with both .mdb and .adp files.

Method #1: Repeat the Calculation within the Sum Expression
Place the calculated expression within the sum expression. To see how this works, open the Order Subform form in the sample database Northwind.mdb in Design view, and then change the ControlSource property of the Order Subtotal text box from:
=Sum([ExtendedPrice])
with the calculation placed inside of the Sum function as follows:
=Sum([UnitPrice] * [Quantity])
View the form in Form view. Note that the calculation is correctly displayed in the Order Subtotal text box in the form footer. Close the Orders Subform form without saving the changes.
Method #2: Compute the Calculated Expression in a Query or in a View
If you compute the calculation, the results will be available for other computations. This method is faster than the first method because the computation does not have to be repeated. To see how this works, follow these steps:
In the sample database Northwind.mdb or in the sample project NorthwindCS.adp, open the Order Details Extended query or view in Design view.


Note that the expression in Northwind.mdb is as follows:
ExtendedPrice: CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100
Note that the expression in NorthwindCS.adp is as follows:
CONVERT(money, [Order Details].UnitPrice*[Order Details].Quantity*(1-[Order Details].Discount)/100)*100
Because these expressions perform their computations at the query level or at the view level, you can now sum the results of this expression at the form level.


Close the query and open the Orders Subform form in Design view.


Note that the Order Subtotal text box in the form footer has the following expression:


=Sum([ExtendedPrice])
Set the Default View property of the form to Single Form. Because this form is based on the Order Details Extended query, the calculated field, ExtendedPrice, appears in the field list, and you can use it for summing values. Note that the calculation is correctly displayed in the Order Subtotal text box in the form footer.





MORE INFORMATION

Steps to Reproduce the Behavior
CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.


Open the sample database Northwind.mdb.


Open the Orders Subform form in Design view.


Add a text box to the detail section, and then set the following properties:


Name: My Extended Price
ControlSource: =[UnitPrice] * [Quantity]

Change the ControlSource property of the Order Subtotal text box from:


=Sum([ExtendedPrice])
to:
=Sum([My Extended Price])
NOTE: The original expression will correctly display the sum of the ExtendedPrice field because this field is being computed as a calculated field in the Order Details Extended query that the form is based on. The ExtendedPrice calculated field in the Order Details Extended query looks as follows:
ExtendedPrice: CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])*100)/100
Change the DefaultView property of the form from Datasheet to Single Form.


View the form in Form view. Note that #Error or #Name? is displayed in the Order Subtotal text box in the form footer.

Close the form without saving the changes.



REFERENCES
For more information about using calculated controls in forms and reports, click Microsoft Access Help on the Help menu, type Calculated Controls in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.


Additional query words: prb pound poundname pounderror poundsign sign

Keywords : kberrmsg kbdta
Issue type : kbprb
Technology : kbAccessSearch kbAccess2000 kbAccess2000Search





Neil Berryman
IT Trainer
neil_berryman@btopenworld.com