Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Unbound Calculating Textbox On Report Not Working 2

Status
Not open for further replies.
LowBrow

LowBrow

Technical User
Jun 1, 2001
100
US
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.
 
Sort by date Sort by votes
I would like to help you out on this....but your logic is wrong....I have copied your code supplied to try to figure out what needs to be done. As you can see, it doesn't work too well.

Code: 
If Reports!rpt_KC_Billing_Ex![SumDaysPaid] = Null Then Exit Sub

If Reports!rpt_KC_Billing_Ex![SumDaysPaid] <= 14 Then
    If Reports!rpt_KC_Billing_Ex![Day_Exempt] = True Then Reports!rpt_KC_Billing_Ex![calcBilling] = 1596
    If Reports!rpt_KC_Billing_Ex![Day_Exempt] = True Then
        If 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
    End If
    If Reports!rpt_KC_Billing_Ex![Day_Exempt] = True Then
        If 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)
    End If
    If Reports!rpt_KC_Billing_Ex![Day_Exempt] = False Then
        If Reports!rpt_KC_Billing_Ex![Hour_Exempt] = True Then Reports!rpt_KC_Billing_Ex![calcBilling] = (Reports!rpt_KC_Billing_Ex.DaysPaid / 15) * 495
    End If
End If
If Reports!rpt_KC_Billing_Ex![SumDaysPaid] >= 15 Then
    If 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
End If
If Reports!rpt_KC_Billing_Ex![SumDaysPaid] >= 15 Then Reports!rpt_KC_Billing_Ex![calcBilling] = 495

So look at the below and fill in so I can assist you. This pseduo code is what I think you are looking for....tell me what value goes in each area and I can help you contruct a function and set it up to meet your needs.

Code: 
If SumDaysPaid is NULL exit and return a zero????
If SumDaysPaid is between 1 and 14 then
    If DayExempt = True Then
        If HourExempt = True Then
            Value = ????
        Else If HourExempt = False
            Value = ????
        End If
    Else If DayExempt = False
        If HourExempt = True Then
            Value = ????
        Else If HourExempt = False
            Value = ????
        End If
    End If
End If
If SumDaysPaid is greater than or equal to 15 then
    If DayExempt = True Then
        If HourExempt = True Then
            Value = ????
        Else If HourExempt = False
            Value = ????
        End If
    Else If DayExempt = False
        If HourExempt = True Then
            Value = ????
        Else If HourExempt = False
            Value = ????
        End If
    End If
End If



=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com
 
Upvote 0 Downvote
Just reformatted using some variables to make it bit more readable.
[blue][tt]
Private Sub GroupFooter4_Format(Cancel As Integer, FormatCount As Integer)
Dim SumPaid As Long
Dim DayExempt As Boolean
Dim CalcBill As Currency
Dim HourExempt As Boolean
Dim TotalHrs As Currency
If SumPaid = Null Then Exit Sub

SumPaid = Reports!rpt_KC_Billing_Ex![SumDaysPaid]
DayExempt = Reports!rpt_KC_Billing_Ex![Day_Exempt]
HourExempt = Reports!rpt_KC_Billing_Ex![Hour_Exempt]
TotalHrs = Reports!rpt_KC_Billing_Ex![TotalHours]

If SumPaid < 15 And DayExempt Then
CalcBill = 1596

ElseIf SumPaid < 15 And DayExempt And Not HourExempt Then
CalcBill = ((TotalHrs / SumPaid) / 5.5) * 495

ElseIf SumPaid < 15 And Not DayExempt And HourExempt Then
CalcBill = (Reports!rpt_KC_Billing_Ex.DaysPaid / 15) * 495

ElseIf SumPaid < 15 And DayExempt And Not HourExempt Then
CalcBill = (((TotalHrs / SumPaid) / 5.5) * 495) * (rpt![DaysPaid] / 15)

ElseIf SumPaid > 14 And Not HourExempt Then
CalcBill = ((TotalHrs / SumPaid) / 5.5) * 495

ElseIf SumPaid > 14 Then
CalcBill = 495

End If

Reports!rpt_KC_Billing_Ex![calCalcBillilling] = CalcBill

End Sub
[/tt][/blue]
The first and third "ElseIf" will never be executed because anything that satisfies them will also satisfy the "If" statement.

The first "ElseIf" and the third "ElseIf" have identical conditions but different calculations.

There is no "Else" clause so items that have
SumPaid < 15 And Not DayExempt And Not HourExempt
will not receive a value (or will be set to zero the way I have structured it.)
 
Upvote 0 Downvote
Hi LowBrow,

Golom beat me to it with the code analysis so I won't repeat what he has already said, but one slight addition. The statement ..

Code: 
[purple]If Reports!rpt_KC_Billing_Ex![SumDaysPaid] = Null Then Exit Sub[/purple]

will never Exit because [purple]If Something = Null[/purple] always evaluates to False

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
 
Upvote 0 Downvote
Thanks Robert, I will try to fill in the blanks:

If SumDaysPaid is Null, then there will be no funding as the client didn't attend (nothing to bill the county for), I wanted the code to exit the sub, leaving the field Null. I didn't think I would have to assign a value. I thought by placing this as the first step in the code, it would make it unnecessary to list the between 1 and 14 days attendance.

15 is the magical number of attending days. If clients attend for 15 or more days, we get max billing, as with 5.5 hours per day. However, some clients are hour and day qualifier exempt. If they attend one day, and one hour for that day, we receive full billing. Also, some clients are hour exempt, but not day exempt (and vice-versa).

Day AND Hour Exempt = 1596

Day Exempt Only = If the average of hours per day is less than 5.5, then the (average of hours per day/5.5)*495. If the average hours per day is = or > 5.5, 495. We are prorated by the proporation of attendance.

Hour Exempt Only = If the SumDaysPaid is 15 days or greater, then the value is 495. If the SumDaysPaid is less than 15 the value is ([SumofDaysPaid]/15)*495.

Neither Day nor Hour Exempt = If SumDaysPaid is 15 or more and the average of hours per day is 5.5 or more, then the value is 495. Otherwise, the value is the (((([TotalHours] / [SumDaysPaid])*5.5)*495)*([SumDaysPaid]/15)). This is convoluted, but here is what it is supposed to represent. We the average attendance by hours (say 72%) times 495. Then this value is prorated further by the average attendance of days per month (say 80%).

I hope this helps you help me. Thanks!


 
Upvote 0 Downvote
Thank you Golom and all! The code works great. There was an error with the last statement:
Reports!rpt_KC_Billing_Ex![calCalcBillilling] = CalcBill

I took it out and everything else works. I'm not sure if this was intentional, as it seems like cut and paste errors.

Thanks again for all your help. It is always appreciated!
 
Upvote 0 Downvote
LowBrow...while it may work, I still think your logic is off. Based on your last post, here is the "logic" as you described:

Code: 
Private Function GetFee(SumDaysPaid As Variant, DayExempt As Boolean, HourExempt As Boolean, AvgHours As Double) As Variant

    If IsNull(SumDaysPaid) Then
        GetFee = Null
        Exit Function
    End If
    Select Case SumDaysPaid
        Case 1 To 14:  ' Do Nothing According To You Last Post
        Case Is >= 15
            If DayExempt And HourExempt Then
                GetFee = 1596
            Else
                If DayExempt And Not HourExempt Then
                    If AvgHours < 5.5 Then
                        GetFee = (AvgHours / 5.5) * 495
                    Else
                        GetFee = 495
                    End If
                Else
                    If SumDaysPaid < 15 Then
                        GetFee = (SumDaysPaid / 15) * 495
                    Else
                        GetFee = 495
                    End If
                End If
            End If
    End Select

End Function

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com
 
Upvote 0 Downvote
Hi LowBrow,

Sorry, I should have been a bit more forthcoming. The correct syntax is ..

Code: 
[blue]If IsNull(Reports!rpt_KC_Billing_Ex![SumDaysPaid]) Then Exit Sub[/blue]

I must say I'm surprised, but happy, if you've got it working fully with such a small change, as Golom's observations are valid and your code (as posted) doesn't reflect your description.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
 
Upvote 0 Downvote
At the risk of sounding like a complete idiot, it did not quite work. I thought I had cleaned out the IIf expressions I put in the control source for the text box. That is what was giving my values for the field in the report. When I started analysing the report, I realized something was amiss, then double-checked the control property. After removing it, I am back to no value displayed in the report.
 
Upvote 0 Downvote
Hi again Robert,
I think there is a disconnect between what I am testing and what you are testing. The cases themselves are not based on the days of attendance or hours of attendance, but whether the individual is Day Exempt, Hour Exempt, Both, or Neither. I did not mean to say that:
Case 1 To 14: ' Do Nothing According To You Last Post

What I meant was that if SumDaysPaid is Null, then it means the person did not attend for the month, therefore we cannot bill for any service. Other than that case (where SumDaysPaid is Null), the formula for billing is dependant on whether they are Days and/or Hours Exempt. Am I confusing this more for you all? It is very confusing and I don't mean to frustrate. The cases are all determined by what an individual has checked for Days Exempt and Hours Exempt.
 
Upvote 0 Downvote
LowBrow...there is a definite disconnect in what you wish to test versus what I am perceiving. My suggestion to you is this:

Several times in previous posts, I have posted your "tests" in either pseudo-code or full code, broken down and labeled a well as possible. Perhaps you can do the same. Build a small table or something that lists all of the tests and what the formulas for each test are. Anything you can do to show us what it is you are trying to accomplish and what you are working from will greatly help us help you. Give us something that clearly shows all the different possibilities and formulas for expected results and we can help you with the coding of the function.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com
 
Upvote 0 Downvote
Hi LowBrow,

I think this is what you want ..

Code: 
[blue]With Reports!rpt_KC_Billing_Ex
  
    If IsNull(![SumDaysPaid]) Then Exit Sub
      
    If ![SumDaysPaid] < 15 Then
      
        If ![Day_Exempt] = True And ![Hour_Exempt] = True Then
            ![calcBilling] = 1596
      
        ElseIf ![Day_Exempt] = True And ![Hour_Exempt] = False Then
            ![calcBilling] = ((![TotalHours] / ![SumDaysPaid]) / 5.5) _
                             * 495
      
        ElseIf ![Day_Exempt] = False And ![Hour_Exempt] = True Then
            ![calcBilling] = (.DaysPaid / 15) * 495
      
        ElseIf ![Day_Exempt] = False And ![Hour_Exempt] = False Then
            ![calcBilling] = ((![TotalHours] / ![SumDaysPaid]) / 5.5) _
                             * 495 * (rpt![DaysPaid] / 15)
             
        End If
          
    Else
        
        If ![Hour_Exempt] = False Then
            ![calcBilling] = ((![TotalHours] / ![SumDaysPaid]) / 5.5) _
                             * 495
        
        Else
            ![calcBilling] = 495
        
        End If
        
    End If
      
End With[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
 
Upvote 0 Downvote
Thanks Robert!
Here goes:

Case 1: Field SumDaysPaid is Null. Return value of 0 (as currency) to field calcBill.

Case 2: Client is Day_Exempt and Hour_Exempt: Return value of 1596 (as currency) to field calcBill.

Case 3: Client is Day_Exempt but not Hour_Exempt AND formula ([TotalHours]/[SumDaysPaid])/5.5 returns a value >= 1: Return value of 495 (as currency) to field calcBill.

Case 4: Client is Day_Exempt but not Hour_Exempt AND formula (case 3) returns value < 1: Multiply returned value by 495 (result in currency) for field calcBill.

Case 5: Client is Hour_Exempt but not Day_Exempt AND formula SumDaysPaid > 14, return value of 495 (as currency) to field calcBill.

Case 6: Client is Hour_Exempt but not Day_Exempt AND formula SumDaysPaid <=14. ([SumDaysPaid]/15)* 495 (result in currency) for field calcBill.

Case 7: Client is NEITHER Day_Exempt NOR Hour_Exempt: Check Hours (case 3 [% of hours] AND case 5 [SumDaysPaid > 14]). If HOURS >=1, and SumDaysPaid > 14, then return value of 495 (as currency) to field calcBill.

Case 8: If Case 7 returns Hours < 1 BUT SumDaysPaid > 14, then Formula: ([TotalHours]/[SumDaysPaid])/5.5 by 495(result in currency) for field calcBill.

Case 9: If Case 7 returns Hours > 1 BUT SumDaysPaid < 15, then Formula: ([SumDaysPaid]/15)*495 (result in currency) for field calcBill.

Case 10: If Case 7 returns Hours < 1 AND SumDaysPaid < 15, then Formula: (((([TotalHours] / [SumDaysPaid])*5.5)*495)*([SumDaysPaid]/15)) (result in currency) for field calcBill.

I hope this is much more clear. If you have any other questions, let me know.
 
Upvote 0 Downvote
This should be closer...let me know:

Code: 
Private Function GetFee(SumDaysPaid As Variant, DayExempt As Boolean, HourExempt As Boolean, TotalHours As Double) As Variant

    ' Case 1
    If IsNull(SumDaysPaid) Then
        GetFee = 0
        Exit Function
    End If
    ' Case 2
    If DayExempt And HourExempt Then
        GetFee = 1596
    ElseIf DayExempt And Not HourExempt Then
        ' Case 3
        If (TotalHours / SumDaysPaid) / 5.5 >= 1 Then
            GetFee = 495
        ' Case 4
        Else
            GetFee = ((TotalHours / SumDaysPaid) / 5.5) * 495
        End If
    ElseIf Not DayExempt And HourExempt Then
        ' Case 5
        If SumDaysPaid > 14 Then
            GetFee = 495
        ' Case 6
        Else
            GetFee = (SumDaysPaid / 15) * 495
        End If
    Else
        ' Case 7
        If (TotalHours / SumDaysPaid) / 5.5 >= 1 And SumDaysPaid > 14 Then
            GetFee = 495
        ' Case 8
        ElseIf (TotalHours / SumDaysPaid) / 5.5 < 1 And SumDaysPaid > 14 Then
            GetFee = ((TotalHours / SumDaysPaid) / 5.5) * 495
        ' Case 9
        ElseIf (TotalHours / SumDaysPaid) / 5.5 < 1 And SumDaysPaid <= 14 Then
            GetFee = (SumDaysPaid / 15) * 495
        ' Case 10
        Else
            GetFee = (((TotalHours / SumDaysPaid) / 5.5) * 495) * (SumDaysPaid / 15)
        End If
    End If
    
End Function

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com
 
Upvote 0 Downvote
Thanks to everyone here. I will be back on this project Thursday, so I will let everyone know how this turned out. Thanks again for your patience and knowledge!
 
Upvote 0 Downvote
Ooops! I put the stars on the wrong post. Anyway, the last code works GREAT! Thanks again to all of you for your patience and effort. I really appreciate it!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Solved
Split Function not working
Replies
3
Views
555
Kennelbloke
Kennelbloke
Moss100
  • Locked
  • Question
Code not working 1
Replies
5
Views
465
Darrylles
Darrylles
Moss100
  • Locked
  • Question
Vba filter not working 3
Replies
7
Views
559
Cole Mendoza
Cole Mendoza
JimStrand
  • Locked
  • Question
Using Report Header (Form control) as Variable in Report Expression 1
Replies
11
Views
2K
dhookom
dhookom
V
  • Locked
  • Helpful tip
Capture Shift Key if Pressed on A form
Replies
3
Views
644
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top