Wish I could but most of the data is in SQL Server and HIPPA Prevents me from providing the data.
I printed the report directly to the printer then used PrintPreview to look at the report and then printed it. In comparing the 2 printed Reports All of the detail matches. The difference is only those 2 fields that are totaled in the On format event of the detail section. Here's the code behind the report.
Code:
Option Compare Database
Option Explicit
Private lngsRaUTHS As Long
Private lngSRAppAuths As Long
Private lngSRDenAuths As Long
Private lngCommercialAuths As Long
Private lngCommAppAuths As Long
Private lngCommDenAuths As Long
Private lngSrmbrship As Long
Private lngCommmbrship As Long
Private lngGTSRauths As Long
Private lngGTSrappauths As Long
Private lngGTSrdenauths As Long
Private lngGTcommappauths As Long
Private lngGTcommdenauths As Long
Private lngGTcommercialauths As Long
Private lngGTsrmbrship As Long
Private lngGTcommmbrship As Long
Private tpdays As Long
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Me.PRODLINE
Case Is = "SENIOR"
Select Case Me.CODE
Case Is = 1
'to calculate the Senior Membership for this provider
lngSrmbrship = lngSrmbrship + Me.MBRS
'to calculate the Senior Membership for the group
lngGTsrmbrship = lngGTsrmbrship + Me.MBRS
lngSRAppAuths = lngSRAppAuths + Me.AUTHS
lngGTSRauths = lngGTSRauths + Me.AUTHS
Case Is = 3
lngSRDenAuths = lngSRDenAuths + Me.AUTHS
lngGTSrdenauths = lngGTSrdenauths + Me.AUTHS
End Select
Case Is = "COMMERCIAL", Is = "COMM-POS"
lngCommercialAuths = lngCommercialAuths + Me.AUTHS
Select Case Me.CODE
Case Is = 1
'To calculate the Commercial membership for the provider
lngCommmbrship = lngCommmbrship + Me.MBRS
'To calculate the Commercial membership for the group
lngGTcommmbrship = lngGTcommmbrship + Me.MBRS
'To calculate the Commercial Approved Authorizations for the provider
lngCommAppAuths = lngCommAppAuths + Me.AUTHS
'To calculate the commercial Approved authorizations for the group
lngGTcommappauths = lngGTcommappauths + Me.AUTHS
Case Is = 3
'To calcluate the Denied Authorizations for the provider
lngCommDenAuths = lngCommDenAuths + Me.AUTHS
'to Calculate the Denied Authorizations for the group
lngGTcommdenauths = lngGTcommdenauths + Me.AUTHS
End Select
'To calculate the Total Authorizations approved or deined for the group
lngGTcommercialauths = lngGTcommercialauths + Me.AUTHS
End Select
End Sub
Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
'the following 6 lines of code set the text box in the report to the appropriate variable
Me.txtsRaUTHS = lngSRAppAuths
Me.TXTDENAUTHS = lngSRDenAuths
Me.txtcommauths = lngCommAppAuths
Me.TXTCOMMDENAUTHS = lngCommDenAuths
Me.txtsrmbrship = lngSrmbrship
Me.txtcommmbrship = lngCommmbrship
'there are 2 divisions in the calculations for 2 of the text boxes in the report one of those tpdays will always have a number > 0 however
'the mbrship could be 0 if that provider has no members for that product line. for example a pediatrician will not have any assigned
'seniors the if statement prevents divide by 0 errors
If lngSrmbrship = 0 Then
Me.txtsrper1000 = 0
Me.txtsrauthpermbr = 0
Else
Me.txtsrper1000 = ((lngSRAppAuths + lngSRDenAuths) / lngSrmbrship) * (365 / tpdays) * 1000
Me.txtsrauthpermbr = ((lngSRAppAuths + lngSRDenAuths) / lngSrmbrship) * (365 / tpdays)
End If
'see comment above however this is for providers that may only have seniors and 0 commercial members
If lngCommmbrship = 0 Then
Me.txtcommper1000 = 0
Me.txtcommauthpermbr = 0
Else
Me.txtcommper1000 = ((lngCommAppAuths + lngCommDenAuths) / lngCommmbrship) * (365 / tpdays) * 1000
Me.txtcommauthpermbr = ((lngCommAppAuths + lngCommDenAuths) / lngCommmbrship) * (365 / tpdays)
End If
'combines the senior approved auths with commercial approved auths to get a total of approved auths
Me.txttotauths = lngSRAppAuths + lngCommAppAuths
'combines the senior denied auths with the commercial denied auths to get the total of denied auths
Me.TXTTOTDENAUTHS = lngSRDenAuths + lngCommDenAuths
'combines senior membership and commercial membership to get a total of membership
Me.txttotmbrs = lngSrmbrship + lngCommmbrship
'this should never be 0 however I took the precaution of including the if statement so the report will run however If the
'total on the report shows as 0 I know I made a mistake on the parameters probably the date range and I need to recheck
If Me.txttotmbrs = 0 Then
Me.txttotauthsper1000 = 0
Me.txttotauthspermbr = 0
Else
Me.txttotauthsper1000 = ((Me.txttotauths + Me.TXTTOTDENAUTHS) / Me.txttotmbrs) * (365 / tpdays) * 1000
Me.txttotauthspermbr = ((Me.txttotauths + Me.TXTTOTDENAUTHS) / Me.txttotmbrs) * (365 / tpdays)
End If
End Sub
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'Reset all of the provider variables back to 0 for each provider
lngCommmbrship = 0
lngCommAppAuths = 0
lngCommDenAuths = 0
lngSRAppAuths = 0
lngSRDenAuths = 0
lngSRAppAuths = 0
lngSrmbrship = 0
lngsRaUTHS = 0
lngCommercialAuths = 0
End Sub
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
'Sets the data in the Report Totals line
Me.txtGTSrAuths = lngGTSRauths
Me.txtGTsrmbrs = lngGTsrmbrship
Me.txtsrdenauths = lngGTSrdenauths
Me.txtgtsrauthsper1000 = (lngGTSRauths / lngGTsrmbrship) * (365 / tpdays) * 1000
Me.txtgtsrauthspermember = (lngGTSRauths / lngGTsrmbrship) * (365 / tpdays)
Me.txtGTcommauths = lngGTcommercialauths
Me.TXTGTCOMMDENAUTHS = lngGTcommdenauths
Me.txtGTCommmbrs = lngGTcommmbrship
Me.txtgtcommauthsper1000 = (lngGTcommercialauths / lngGTcommmbrship) * (365 / tpdays) * 1000
Me.txtgtcommauthspermbr = (lngGTcommercialauths / lngGTcommmbrship) * (365 / tpdays)
Me.txtgtauths = lngGTSRauths + lngGTcommercialauths
Me.txtgtmbrs = lngGTsrmbrship + lngGTcommmbrship
Me.txtgtautsper1000 = (Me.txtgtauths / Me.txtgtmbrs) * (365 / tpdays) * 1000
Me.txtGTtotauthspermbrs = (Me.txtgtauths / Me.txtgtmbrs) * (365 / tpdays)
End Sub
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
'sets the variable tpdays to the number of days between the start date parameter and the end date parameter.
'Normally this is run for the preceding quarter of when the report is run but it could potentially be for a year or
'half a year
tpdays = DateDiff("d", Forms!form1!txtstart, Forms!form1!txtend) + 1
End Sub