SubTotal and Grand totals on forms

[SmilingPolitely]

I have a form with the following fields:
CheckInDate
CheckOutDate
CostPerNight (computed value drawn from table of providers)
Discount
Extras

I calculate a field
SubTotal=DateDiff("d",CheckInDate,CheckOutDate)*CostPerNight-Discount+Extras

Works OK up to here. The correct values are being displayed.


Now, I want to display the grand total in the form footer.

I tried GrandTotal=Sum([SubTotal]) but as the SubTotal is a computed
value and includes a computed value itself, an #Error is generated.

How do I get around this problem?

Any help is appreciated.

 

[Allen Browne]

You can repeat the entire expression, i.e.:
=Sum(DateDiff("d",CheckInDate,CheckOutDate)*CostPerNight-Discount+Extras)

It might be easier to create a query to use as the RecordSource of your
form. Type the calculated field into the Field row in query design, e.g.:
Amount: DateDiff("d",CheckInDate,CheckOutDate)*CostPerNight-Discount+Extras

You can now show the Amount field on your form, and in the form footer:
=Sum([Amount])