Unbound Calculating Textbox On Report Not Working

[LowBrow]

I have tried tackling this from several different angles and none work. I have searched through the forums here and have found some similar issues, but none with solutions that fit. I got the basic logic to work in an IIF statement as the control source, but the full statement is too complex for IIF. So here is the problem:
I have report that produces a monthly fees for service report based on client attendance. Clients have different funding levels, and each funding level has different attendance requirements which determine how much money is received for services. Here are the basic If ... Then ... ElseIf statements I have been using. I have placed this in the On Format of one the client group footer of the report.

Private Sub GroupFooter4_Format(Cancel As Integer, FormatCount As Integer)
If Reports!rpt_KC_Billing_Ex![SumDaysPaid] = Null Then Exit Sub
If Reports!rpt_KC_Billing_Ex![SumDaysPaid] < 15 And Reports!rpt_KC_Billing_Ex![Day_Exempt] = True Then
Reports!rpt_KC_Billing_Ex![calcBilling] = 1596
ElseIf Reports!rpt_KC_Billing_Ex![SumDaysPaid] < 15 And Reports!rpt_KC_Billing_Ex![Day_Exempt] = True And Reports!rpt_KC_Billing_Ex![Hour_Exempt] = False Then
Reports!rpt_KC_Billing_Ex![calcBilling] = ((Reports!rpt_KC_Billing_Ex![TotalHours] / Reports!rpt_KC_Billing_Ex![SumDaysPaid]) / 5.5) * 495
ElseIf Reports!rpt_KC_Billing_Ex![SumDaysPaid] < 15 And Reports!rpt_KC_Billing_Ex![Day_Exempt] = False And Reports!rpt_KC_Billing_Ex![Hour_Exempt] = True Then
Reports!rpt_KC_Billing_Ex![calcBilling] = (Reports!rpt_KC_Billing_Ex.DaysPaid / 15) * 495
ElseIf Reports!rpt_KC_Billing_Ex![SumDaysPaid] < 15 And Reports!rpt_KC_Billing_Ex![Day_Exempt] And Reports!rpt_KC_Billing_Ex![Hour_Exempt] = False Then
Reports!rpt_KC_Billing_Ex![calcBilling] = (((Reports!rpt_KC_Billing_Ex![TotalHours] / Reports!rpt_KC_Billing_Ex![SumDaysPaid]) / 5.5) * 495) * (rpt![DaysPaid] / 15)
ElseIf Reports!rpt_KC_Billing_Ex![SumDaysPaid] > 14 And Reports!rpt_KC_Billing_Ex![Hour_Exempt] = False Then
Reports!rpt_KC_Billing_Ex![calcBilling] = ((Reports!rpt_KC_Billing_Ex![TotalHours] / Reports!rpt_KC_Billing_Ex![SumDaysPaid]) / 5.5) * 495
ElseIf Reports!rpt_KC_Billing_Ex![SumDaysPaid] > 14 Then Reports!rpt_KC_Billing_Ex![calcBilling] = 495

End If
End Sub

I would prefer to use this in a Function, but I have not gotten that to work either. What happens is either I get #Name?, $1596 for all cases, or a blank field. I think part of the problem is trying to retrieve the value for Hour_Exempt or Days_Exempt (which are check-boxes). I tried calling a query which determines the days and hours for the given period per client, and contains teh check-boxes to test, but this was unsucessful, too.
Any insight would be much appreciated. I have gotten a lot of help here over the years! Thanks to you all.

 

[dhookom]

You can write a function that has Arguments for each of the required field values needed in the function. A call to the function might look like:
=YourFunction([SumDaysPaid], Day_Exempt, calcBilling, Hour_Exempt, ...)

In a group or report footer, you can then use:
=Sum(YourFunction([SumDaysPaid], Day_Exempt, calcBilling, Hour_Exempt, ...))
Keep in mind that the name of the control can not also be the name of a field.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LowBrow]

Thanks Duane!
This is the missing part to a response I got in Access Modules!