Results 1 to 4 of 4

Tracking values in Access reports in Code

  1. #1
    dahcjohnson is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Posts
    2

    Tracking values in Access reports in Code

    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

    Click image for larger version. 

Name:	no sum.jpg 
Views:	14 
Size:	162.5 KB 
ID:	7576

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,887
    Are you aware that controls on a Report can Sum? You can also create additional invisible controls that do that summing for you.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    dahcjohnson is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Posts
    2
    As I was showing in my screen shot, there is no option to sum on the column that I want to sum. (The only option that shows up is to count the records).

    And for using the hidden fields, if that is the way to do it I can do it that way. I started to do it that way, then found that I would have 18 hidden fields. Is there a good way to use these? Where do I put them all? Just overlay them overtop of one another??

    Thanks.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,887
    Quote Originally Posted by dahcjohnson View Post
    And for using the hidden fields, if that is the way to do it I can do it that way. I started to do it that way, then found that I would have 18 hidden fields. Is there a good way to use these? Where do I put them all? Just overlay them overtop of one another??

    Thanks.
    Since they are hidden, they can actually be anywhere you want to put them as long as it doesn't screw up the format of your report. Stacking them would work just fine but you may want to have them easily accessable until you get them working as you want.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 11-08-2012, 10:09 AM
  2. Inventory tracking with Ms Access (newbie)
    By sanlen in forum Access
    Replies: 5
    Last Post: 02-14-2012, 07:27 AM
  3. Replies: 1
    Last Post: 11-08-2011, 07:44 PM
  4. Access for tracking statistics
    By rjbeck52 in forum Access
    Replies: 2
    Last Post: 07-06-2011, 05:48 PM
  5. Replies: 1
    Last Post: 06-06-2011, 06:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums