calcualting a sum of a field which has been calculated (1 Viewer)

[iainmid]

On my booking order subform (subform of booking form linked by booking no) i have booking no, property no, price per day, no of days, total cost and total property cost. All my fields are numeric and the total coat and total property cost are situated in the subfooter of the form and grouping is by the property no.
total cost shows the price for that property no depending on amount of days
total cost is worked out: =[price per day]*[no of days]

total property cost needs to show the sum of all the total costs because more than one property can be booked on the same booking no.
i have tried =sum([total cost]) but it just shows 0. i think this might be because total cost is a derived value because when i just do =[total cost] the correct value appears from the coresponding field it just seems to have a problem when using the SUM function.
I have also tried =Sum([price per day]*[no of days]) but this also shows 0.
Is there any way of getting round this?

Thanks

 

[boblarson]

Are you using datasheet view in your subform, or continous? If using continuous, or single, you can have a form footer and put the sum calculation there and then refer to that text box on the main form.

 

[Ron_dK]

Ensure that the data in your subform is based on a query.
One of the query fields should be : Totalcost : ([price per day]*[no of days])

Then on the form footer you can add a textbox with a controlsource something like : =Sum([Totalcost])

Hth

 

[iainmid]

hi i am using single form view and have all ready got the sum calculation in the form footer refering to the field on the main form but the calculation is not working

 

[boblarson]

What is the name of your:
1. Main Form
2. Sub Form and subform CONTAINER
3. Text box on Sub Form with the calculation

And how is the main form text box referring to those?

 

[iainmid]

1. the main form is booking this is going to pick up on the total property cost from the footer in the booking order subform.

2. subform is called booking order subform this issue is total property cost not calculating in the footer of this form. (not sure what u mean by subform container)

3. on the details section of this form is price per day, no of days and total cost.
total cost has in the control source =[price per day]*[no of days]
in the footer the text field total property cost has in the control source =sum([total cost]) this then displays #error. i have also tried =sum([price per day]*[no of days]) this displays 0

 

[boblarson]

What I mean by Subform CONTAINER is the control that houses the subform on the main form. When you place a subform on a main form, it isn't just the subform, but a container which holds the subform and performs the operations between the main form and subform. So, when referring to a subform from a main form, you need to reference the subform CONTAINER name instead of the subform name, unless they are the same (which is the case if you add the subform using the wizard).

So, to reference a control from a subform you would use:

Forms!YourMainFormNameHere.YourSubformContainerNameHere.Form.YourControlNameHere

And to set a controlsource for a textbox on a main form to refer to the subform would be:
=[Forms]![Booking].[Form].[Booking Order].[Form].[WhateverYourTextBoxNameIs]

 

[iainmid]

the problem i'm having is not on the main form its the calculation on the subform in the total property cost field.

 

[boblarson]

Then, in your subform footer, you need to set the controlsource of the text box to be
=Sum(([price per day]*[no of days]))

 

[iainmid]

when i do this it displays 0 instead of the correct value