Calculated Control in Subform Footer Resulting in #Error

Tophan

Registered User.
Local time
Today, 04:32
Joined
Mar 27, 2011
Messages
389
Good night,

I have been struggling to show a subtotal in the footer of an invoicing subform.

The subform has the following text boxes

[StartTime]
[EndTime]
The standard rate is $50.00 for the first hour or part thereof; $50.00 per hour thereafter.
I have calculated the time elapsed using the DateDiff formula in text box named [TTLTime]. To calculate the cost per record I used the following formula in text box [SubTotalHrs]

=(([TTLTime]-1)*50)+50

I am now trying to show the subtotal in the subform footer and every formula I try is returning #Error - Sum((([TTLTime]-1)*50)+50); sum([SubTotalHrs]); Sum(Nz([SubTotalHrs],0))

Nothing is working and I don't know why.

Been searching this forum and tried a couple suggestions but still nothing working.

Would appreciate your suggestions.

Thanks
 
make text boxes that have the Sum formula
txtTotHrsSum: = sum([SubTotalHrs])

then in your other text box ,make the formula use the txtbox names:
=txtBox1 - txtTotHrsSum

if you need sums of data columns, then you need a query that does the sum.
 
Thanks but still getting #Error
 
Aggregate functions (Sum, Avg, Count) must reference fields, not control names. So either do the TTLTime and SubTotalHrs calculations in query or repeat the entire calc that produces the SubTotalHrs within the Sum, including the DateDiff.
 
THANK YOU! Including the datediff formula worked! So below is the final formula

=Sum(((DateDiff("n",[StartTime],[EndTime])/60)-1)*50)+50

Thank you so much :)
 
Made a slight change to the formula in the subform; below is the corrected final sub-total formula

=50*(Sum(DateDiff("n",[StartTime],[EndTime])/60)-1)+50

Thanks again for your help
 

Users who are viewing this thread

Back
Top Bottom