SubForm, Total and Subformal, two calculations

[mamely]

Hi,

I have a subform where the Total is calculated for the items ordered using quantity and itemprice..this is working fine.

However, the subform is the subform of the Orders main form and I need to calcualte the SubTotal (so =sum([Total]) and this isn't working.... it comes up as an error....

I tried in in the sub form's footer and the main form's footer... any ideas?

Is this because I am trying to use a calculation for another calculation??:banghead:

 

[pbaldy]

Yes, you can't sum a calculated field. Try

=Sum(quantity * itemprice)

or move the total calculation out to the form's source query so it's a field you can total.

 

[constableparks]

Glad to see this post - even though it means I can't do what I'm trying to do. At least I'm not wasting any more time. Thanks, Paul, for your suggestion. It points me to look at my subtotals and grand totals in a different way.

 

[pbaldy]

No problem. It would have been more accurate of me to say you can't sum a calculated control.

 

[constableparks]

I have a solution for what I am seeing, but I'm looking for a better solution. I have two subtotals [note: not the actual field names]:
=Format((InHouse_Hours * Labor_InHouse_Wage), "$#,###.00")
=Format((InField_Hours * Labor_InField_Wage), "$#,###.00")

I want to add those together and I can easily do this with:
=Format((InHouse_Hours * Labor_InHouse_Wage) + (InField_Hours * Labor_InField_Wage), "$#,###.00")

But if I have a LOT of different subtotals to add together for a Grand Total - this could get a bit messy. I'm sure there is an easier (more elegant) solution?

 

[pbaldy]

You can add the text boxes:

=FirstTextbox + SecondTextbox

I'd just format the text boxes rather than use the function.

 

[constableparks]

That works - with the caveat that the Format() function is not used and the textboxes are set to "Currency" format. Such an easy solution.... just what I was looking for. Thank you.

Just for future reference for anyone stumbling on this:

Textbox1:
=[InHouse_Hours] * [Labor_InHouse_Wage] >>>>result is $300.00

Textbox2:
=[InField_Hours] * [Labor_InField_Wage] >>>>result is $1,000.00

Textbox3:
=[Textbox1] + [Textbox2] >>>>result is $1,300.00

If you try to use the Format() function \\EG: =Format((field1 * field2), "$#,####.00)\\ like I was doing, Textbox3 will have the result: $300.00$1,000.00

 

[pbaldy]

No problem. The Format() function returns text, so you get a concatenated result instead of a math result.