need to put a total in report ... not simple sum of previous bands

[tw]

I need to put in overall report totals for client contribution, client hours
billed, and client hours not billed. I have in the client summary band the
following controls

'sums data from the detail band
textbox control >TotalHours =Sum([SumOfHours])

'sums the maximum value of the (SumOfHours from the detail band - 16) or 0
textbox control >MonthlyBilled =Sum(myMax(([SumOfHours]-16),0))

'subtracts the controls above to get ClientHoursNotBilled
textbox control >ClientHoursNotBilled =[TotalHours]-[MonthlyBilled]

'function to calc the tot monthly hours the client is authorized values
passed to func come from query datasrc of report
textbox control >MonthlyAuth =calcMAuth([cl-pk client id],[pcslog dos])

'from the query datasource of the report this is the maximum monthly client
contribution
textbox control >ClientCont =[CL Client Contribution]

'calc to determine the per hour charge for the client based on their monthly
contribution and their authorized hours
textbox control >PerHour =IIf([MonthlyAuth]<>0,[ClientCont]/[MonthlyAuth],0)

'amt billed to client for the month is the minimum of the hours billed * the
per hour rate or the maximum contribution
textbox control >Contribution
=myMin(([PerHour]*[MonthlyBilled]),[ClientCont])

The above all works fine in the summary band for the client where it should
be. Now I need to total on the report for all clients the information of
client contribution, client hours billed, and client hours not billed,

I can total the client HoursBilled and NotBilled fine because I just sum the
detail band data, but the total client contribution uses a function to
determine the total authorized, then unit price prior to calculating the
contribution amount. If I put the function in the query to have the
information available at the detail band level, then the function will be
called countless time and slow the query. I can't put the function in the
report band of the report, because the function works on an individual
clientID. I tried putting a variable in the report like this, but the
report total was double what it should be. I could just divide by 2, but
there must be something wrong. Is there a better event to put the
calculation?

Option Compare Database
Option Explicit
Dim dblTotalContribution As Double

Private Sub GroupFooter5_Format(Cancel As Integer, FormatCount As Integer)
dblTotalContribution = dblTotalContribution + Me.txtClientCont
End Sub

Private Sub Report_Open(Cancel As Integer)
dblTotalContribution = 0#
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Me.RptContribution = dblTotalContribution
End Sub

 

[tw]

I solved my own issue. Thanks.