I am new to creating reports in Access, so please bare with me.
I am trying to track values in a group footer. to use them at the end of the report.
I have just made a couple labels at the end and I want to put the values that I have tracked along the way into these labels.
Basically for the report footer I have 2 rows of sums ( a total for the entire report, and a total for the entire report minus 1 category)
In the Groupfooter_format I am tracking the total sum for the category I want to exclude, as well as tracking the total sum for the entire report.
Than in the ReportFooter_Format I am updating the Label.Caption with the numbers I am tracking.
One problem I have come across is that if I am in "Print Preview" the report looks good, but if I go to "PDF or XPS" the file, the total numbers get screwed up.
Is there a better way to track these sums? ( I tried doing it in Layout view, but it doesn't look like I can sum on a Header value) (plus it wouldn't be able to track the category that I want to omit from one of the totals.)
I have attached an image of a couple views to help get a good idea of what I have made.
I have also attached all my code that I made.
Thanks everyone.
Code:
Option Compare Database
Dim adminTotal As Double
Dim adminBillable As Double
Dim adminNonBillable As Double
Dim adminAdmin As Double
Dim adminBD As Double
Dim adminTR As Double
Dim adminInternal As Double
Dim adminBenifits As Double
Dim adminTimeToBank As Double
Dim adminUtilization As Double
Dim adminUtilizationWOB As Double
Dim allTotal As Double
Dim allBillable As Double
Dim allNonBillable As Double
Dim allAdmin As Double
Dim allBD As Double
Dim allTR As Double
Dim allInternal As Double
Dim allBenifits As Double
Dim allTimeToBank As Double
Dim allUtilization As Double
Dim allUtilizationWOB As Double
Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
On Error Resume Next
allTotal = allTotal + grpTotal.Text
allBillable = allBillable + grpBillable.Text
allNonBillable = allNonBillable + grpNonBillable.Text
allAdmin = allAdmin + grpAdmin.Text
allBD = allBD + grpBD.Text
allTR = allTR + grpTR.Text
allInternal = allInternal + grpInternal.Text
allBenifits = allBenifits + grpBenefits.Text
allTimeToBank = allTimeToBank + grpTimeToBank.Text
allUtilization = allUtilization + grpUtilization.Text
allUtilizationWOB = allUtilizationWOB + grpUtilizationWOB.Text
On Error GoTo ErrHandler:
Dim catval As String
catval = grpCategory.Text
If catval = "Admin Total" Then
adminTotal = grpTotal.Text
adminBillable = grpBillable.Text
adminNonBillable = grpNonBillable.Text
adminAdmin = grpAdmin.Text
adminBD = grpBD.Text
adminTR = grpTR.Text
adminInternal = grpInternal.Text
adminBenifits = grpBenefits.Text
adminTimeToBank = grpTimeToBank.Text
adminUtilization = grpUtilization.Text
adminUtilizationWOB = grpUtilizationWOB.Text
End If
ErrHandler:
catval = ""
' go back to the line following the error
Resume Next
End Sub
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
lblFooterTotal.Caption = allTotal
lblFooterBillable.Caption = allBillable
lblFooterNonBillable.Caption = allNonBillable
lblFooterAdmin.Caption = allAdmin
lblFooterBD.Caption = allBD
lblFooterTR.Caption = allTR
lblFooterInternal.Caption = allInternal
lblFooterBenefits.Caption = allBenifits
lblFooterTimeToBank.Caption = allTimeToBank
lblFooterUtilization.Caption = Round((allBillable / allTotal) * 100, 0) & "%"
lblFooterUtilizationWOB.Caption = Round((allBillable / (allTotal - allBenifits)) * 100, 0) & "%"
lblFooterAdminTotal.Caption = allTotal - adminTotal
lblFooterAdminBillable.Caption = allBillable - adminBillable
lblFooterAdminNonBillable.Caption = allNonBillable - adminNonBillable
lblFooterAdminAdmin.Caption = allAdmin - adminAdmin
lblFooterAdminBD.Caption = allBD - adminBD
lblFooterAdminTR.Caption = allTR - adminTR
lblFooterAdminInternal.Caption = allInternal - adminInternal
lblFooterAdminBenefits.Caption = allBenifits - adminBenifits
lblFooterAdminTimeToBank.Caption = allTimeToBank - adminTimeToBank
lblFooterAdminUtilization.Caption = Round(((allBillable - adminBillable) / (allTotal - adminTotal)) * 100, 0) & "%"
lblFooterAdminUtilizationWOB.Caption = Round(((allBillable - adminBillable) / ((allTotal - adminTotal) - (allBenifits - adminBenifits))) * 100, 0) & "%"
End Sub