How to sum from a field thats been derived

[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.
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.. Is there any way of getting round this?

Thanks

 

[boblarson]

You will have to use

=Sum([price per day]*[no of days])

 

[iainmid]

Hi Bob

I've tried that as well but it still shows 0

 

[ThomoNZ]

Hi,

Are the fields specified above (i.e. price per day, no of days) Text or Numeric?

If they're numeric the code supplied above should work fine otherwise you'll be finding some difficulty.

You'll want to ensure you are grouping by the property no also.

Cheers
Thomo

 

[boblarson]

Also, I just thought of this, but is your total text box on the SubForm Footer, or on the main form? If on the main form, it is a little trickier.

 

[iainmid]

Hi

In answer to your questions all the fields price per day, no of days and total cost are all specified as numeric fields and grouping is by property no but my total cost and total property cost are all in the details section on the booking order subform. do i need to move them to the footer of this form.

 

[ThomoNZ]

Hi,

I assume the Total Cost populates as the form is being completed by the user?

It may be that you need to include a refresh or requery command on exit or the change event of one of these fields (property no, price per day, no of days).

or

If the fields are all bound to a table you could also do something in the change event like:

'****************************************

Dim rs as DAO.Recordset
Dim db as DAO.Database

Dim dTotal as Double ' Total Cost

Set db = CurrentDB

Set rs = db.OpenRecordset(" [Table name] ")

dTotal = rs.fields("[price per day]") * rs.fields("[no of days]")

me.[Total Cost] = dTotal

*****************************************

The above (or a variation on the above) might help?

Cheers
ThomoNZ