Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    St. Louis, Missouri
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum Values in Group Footer using Code (2000)

    I am just finishing up a database that I was able to put together by scouring other people's posts in the Lounge. I now have a problem that I cannot seem to find the solution to, so this will be my first post.

    I have a report in my database that is a modified version of the EmployeeSales Report from the Sample Solutions9 database. I am using it to display time card information.

    The report uses a crosstab query to display the number of dollars charged against a job number (I am using this as a grouping level within the report) and then to display which cost code was used within the job number. My columns are each payroll week. The report sums across and down, but I cannot get it to sum on each value for the Job number. When I insert a text box directly on the report, no value is calculated and when I use code in the On Print of the Group Footer, the sum displays, but it is a running total for the payroll week and not just for the job number.

    I am hoping someone is familiar with this sample database and may have encountered a similar need.
    Thank you!

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum Values in Group Footer using Code (2000)

    If i understand your problem, you have to group on job number, put the textbox in the footer of the group and set the running sum property of the text box to Over Group.
    Francois

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Location
    St. Louis, Missouri
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum Values in Group Footer using Code (2000)

    When I do that, I receive an error that the Microsoft Jet database engine does not recognize " as a valid field name or expression. I believe I have to do it through code because all of the values and totals are calculated using code with this sample report. Here is what I have for that field:

    Option Compare Database 'Use database order for string comparisons.
    Option Explicit

    ' Constant for maximum number of columns Payroll Summary query would
    ' create plus 1 for a Totals column.
    Const conTotalColumns = 11

    ' Variables for Database object and Recordset.
    Dim dbsReport As DAO.Database
    Dim rstReport As DAO.Recordset

    ' Variables for number of columns and row and report totals.
    Dim intColumnCount As Integer
    Dim lngRgColumnTotal(1 To conTotalColumns) As Long
    Dim lngReportTotal As Long

    Private Sub GroupFooter0_Print(Cancel As Integer, PrintCount As Integer)
    Dim intX As Integer

    ' Place column totals in text boxes in report footer.
    ' Start at column 3 (first text box with crosstab value).
    For intX = 3 To intColumnCount
    Me("JobNumber" + Format(intX)) = lngRgColumnTotal(intX)
    Next intX

    ' Place grand total in text box in report footer.
    Me("JobNumber" + Format(intColumnCount + 1)) = lngReportTotal

    ' Hide unused text boxes in report footer.
    For intX = intColumnCount + 2 To conTotalColumns
    Me("JobNumber" + Format(intX)).Visible = False
    Next intX

    End Sub

    This code sums on the JobNumber field, but for each value, it is adding the previous JobNumber's value to it. I need that value to just be for the current JobNumber field.
    Thanks for any help you can give.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum Values in Group Footer using Code (2000)

    In the code I don't see how you are calculating lngRgColumnTotal(intX).
    It's hard to say something without having all the code.
    If you want and the data is not sensitive, attach the database, or if it's to big, send it to my e-mail (see my profile).
    Francois

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

    Re: Sum Values in Group Footer using Code (2000)

    Hello Erin,

    I have attached a demo based on the Access 97 Solutions database.

    It contains a report based on a crosstab query with sums over a group.

    Captions, comments and date settings are all in Dutch. I don't have the time to translate it into English, but by experimenting with it I hope you will see what it does.

    The method Microsoft used in the original EmployeeSales example was horribly clumsy and slow. Several people on this forum and elsewhere have suggested improvements; they have been incorporated in this demo.

    HTH, Hans
    Attached Files Attached Files

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum Values in Group Footer using Code (2000)

    I added code in the on print of footer 0

    For intX = 3 To intColumnCount
    lngRgColumnTotal(intX) = 0
    Next intX
    lngReportTotal = 0

    to reset the values for each job.
    Then I create two variables

    Dim lngReportTotal As Long
    Dim lngReportGrandTotal As Long

    to store the sum over the report.
    In the detail section I add two lines to calculate these Grandtotal values
    and in the footer of the report I set the textboxes to the GrandTotal values

    Hope this helps
    I send the modified database by mail as it's to big for attachment
    Francois

  7. #7
    New Lounger
    Join Date
    Nov 2001
    Location
    St. Louis, Missouri
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum Values in Group Footer using Code (2000)

    That did it! Thank you so much Francois. <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>
    Hans, I will review your attachment to see if there are any improvements I can make on the code.

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

    Re: Sum Values in Group Footer using Code (2000)

    In case people are interested: in my previous post I attached a demo database in Dutch. You will find the English language version attached to this post.
    Attached Files Attached Files

  9. #9
    New Lounger
    Join Date
    Nov 2001
    Location
    St. Louis, Missouri
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum Values in Group Footer using Code (2000)

    Francois,
    The report contains dollar values and I just noticed that the totals fields are rounding the values beyond the decimal point. Any ideas on how to get them to sum up to two decimal places? <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum Values in Group Footer using Code (2000)

    Erin,
    In the code window of the report, you have four variable Dim ... As Long.
    Change this to Dim ... As Single
    Also In the Sub Detail_Print you have
    Dim lngRowTotal As Long
    Change it to
    Dim lngRowTotal As Single
    Francois

  11. #11
    New Lounger
    Join Date
    Nov 2001
    Location
    St. Louis, Missouri
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum Values in Group Footer using Code (2000)

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    Thanks for the quick reply.

    Obviously I am not a code writer! I had no idea what I was looking for. That took care of it.

    Thanks Again

Posting Permissions

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