Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Capture Total in footer (2k)

    I need to sum the value in a control in the footer, but cannot see the forest for the trees. In a nutshell, the txtTotCoLiab is calculated when the report runs and is called by the On Print event (code listed below) To simplify the code for posting, I made the retention a constant (150000). I am struggling now trying to get the sum of txtTotCoLiab print in the footer.

    Is there enough information posted to give you an idea? Any ideas are greatly appreciated.

    = = = = = = Start Code = = = = = = = =

    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

    Dim curTotalIncurred As Currency
    Dim curRetention As Currency
    Dim curTotalPaid As Currency
    Dim curTotOutStandReserve As Currency
    Dim status as integer

    ' Current Retention will be changed at a later date to be a variable from the insurance table
    curRetention = 150000

    curTotalIncurred = [MedicalCost] + [Indemnity] _
    + [LegalCost] + [PropertyDamage] + _
    [OtherCosts] + [OSMedReserve] + _
    [OSIndemnityReserve] + [OSLegalReserve] _
    + [OSPropertyReserve]


    curTotalPaid = [MedicalCost] + [Indemnity] + _
    [LegalCost] + [PropertyDamage] + [OtherCosts]

    curTotOutStandReserve = [OSMedReserve] + [OSIndemnityReserve] _
    + [OSLegalReserve] + [OSPropertyReserve]

    If ClaimClosed = True Then
    Status = 2
    Else Status =1
    End if

    Select Case

    Case 1
    If curTotalIncurred <= curRetention Then
    Me.txtTotCoLiab = curTotOutStandReserve
    End If
    If curTotalIncurred > curRetention Then
    Me.txtTotCoLiab = curRetention - curTotalPaid
    End If

    Case 2
    If curTotalIncurred <= curRetention Then
    Me.txtTotCoLiab = (curTotOutStandReserve)*LDF
    End If
    If curTotalIncurred > curRetention Then
    Me.txtTotCoLiab = (curRetention - curTotalPaid)*LDF
    End If

    End Select

    End Sub

    = = = = = = End Code = = = = = = = =

    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Capture Total in footer (2k)

    1. I don't understand what Status is for.
    2. You have a Select Case statement, but there is nothing after Select Case. Should Status have been there? If so, you might as well have used ClaimClosed directly.
    3. It is probably more efficient to calculate the value that now goes into txtTotCoLiab in the record source of the query. Then, you can simply use =Sum([Something]) as control source of the text box in the report footer. (I assume you meant report footer)

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Capture Total in footer (2k)

    Sorry Hans, the Select Case Statement should have been Select Case using "status".

    The reason I couldn't (at least not with my mediocre access experience) calculate it in the query is that there are different scenarios depending on the claim status (Open or Closed) and then comparing the amount paid to the retention. Hence the if statements in the two Case statements.

    Does that make sense?

    Thanks for taking time out to look it over.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Capture Total in footer (2k)

    You can use custom VBA functions in queries too, but perhaps you don't even need one. In a query based on the tables you are using, create calculated fields:

    curTotalPaid: [MedicalCost] + [Indemnity] + [LegalCost] + [PropertyDamage] + [OtherCosts]

    curTotOutStandReserve: [OSMedReserve] + [OSIndemnityReserve] + [OSLegalReserve] + [OSPropertyReserve]

    curTotalIncurred: [curTotalPaid] + [curtotOutStandReserve]

    curRetention: 15000

    Save this query and create a new one based on it. To this query, add the fields you need, plus another calculated field:

    CalcTotCoLiab: IIf([curTotalIncurred] > [curRetention], [curRetention] - [curTotalPaid], [curTotOutStandReserve]) * IIf([ClaimClosed], [LDF], 1)

    Use this query as record source for the report.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Capture Total in footer (2k)

    You are AWESOME!!!!

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •