Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining Subtotals in Report (Access 2002 SP2)

    Can someone get me started??? I have a main report with three subreports. Each of those has a sum function in its footer to give a group total. Now I want to add the three totals for a combined total. I have used subforms, but not subreports! I have read and re-read the information about referenceing subreport controls, then the examples show subforms and I find myself in over my head! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    Main report is called Invoice
    My first subreport is called Total of Fees and the sum field is named Sum Fees. It is a sum of values in a table.
    Second one is Mileage Totals and sum field named Sum Mileage (this is a calculated field in a query)
    Thirs is Other Charges with sum field named Sum Other. It is a sum of values from a table.

    TIA
    egghead

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

    Re: Combining Subtotals in Report (Access 2002 SP2)

    Create a text box on Invoice with Control Source

    =[Total of Fees]![Sum Fees]+[Mileage Totals]![Sum Mileage]+[Other Charges]![Sum Other]

    Note: you must use the names of the subreports as controls on the main report Invoice. This is not necessarily the same as the name of the subreports in the database window. To find out the control names, open the main report in design view, and click once on a subreport. The control name will be visible in the Properties window and in the Object dropdown list in the toolbar.

  3. #3
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Subtotals in Report (Access 2002 SP2)

    I'm getting an error on my total. I have double checked the subreport names and the names of the totals from the subreports. The totals in each subreport of sum functions in their footers, does that affect my grand total on the Invoice?

    I will attach the db in case that helps.

    TIA
    Attached Files Attached Files
    egghead

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

    Re: Combining Subtotals in Report (Access 2002 SP2)

    The problem is that for most clients, at least one of the subreports contains no records. If the subreport is empty, the Totals control simply doesn't exist, so referring to it results in #Error. You can use the DSum function combined with the Nz function to get the desired result. Set the Control Source of Sum of Charges on the Invoice report to

    =Nz(DSum("[Total Fees]","[Time Log Fee Totals]","[Client Name] = " & Chr(34) & [Client Last Name 1st Initial] & Chr(34)),0)+Nz(DSum("[Total]","[Mileage Totals]","[Client Name] = " & Chr(34) & [Client Last Name 1st Initial] & Chr(34)),0)+Nz(DSum("[Amount]","[Client Charges]","[Client Name] = " & Chr(34) & [Client Last Name 1st Initial] & Chr(34)),0)

    Note: the browser will break this expression into several lines, but it should be one humongous expression.

  5. #5
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Subtotals in Report (Access 2002 SP2)

    The word "humongous" seems to be an understatement here!!!

    I would appreciate a quick definition of the nz, the chr(34) and what they do...

    TIA!!! <img src=/S/hiding.gif border=0 alt=hiding width=70 height=24>

    I will try it, in the meantime!
    egghead

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

    Re: Combining Subtotals in Report (Access 2002 SP2)

    Nz is a function that takes two arguments: Nz(a, [img]/forums/images/smilies/cool.gif[/img] will return a, unless a is Null; in that case it will return b. My humongous expression contains three parts of the form

    Nz(DSum(...),0)

    If DSum(...) is not null, the DSum value will be returned, but if that is Null (empty), 0 will be returned instead. The DSum function takes three arguments: a field name, a table/query name and a where-condition. In the first DSum, the where-condition is

    "[Client Name] = " & Chr(34) & [Client Last Name 1st Initial] & Chr(34)

    Since Client Name is a text field, the value to compare it with must be enclosed in quotes. But you can't just put a quote inside a string, that confuses the Visual Basic interpreter. One workaround is to concatenate with Chr(34); ASCII code 34 is the double quote ".

  7. #7
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Subtotals in Report (Access 2002 SP2)

    Would I be able to do something with If statements in the subreports to get them to show the value or a 0 and then be able to use a simpler expression for the "grand" total? I would actually like the invoice to show a 0 if there are no charges.
    egghead

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

    Re: Combining Subtotals in Report (Access 2002 SP2)

    The problem with your approach is that if there are no records for the subreport for a certain client, the subreport isn't displayed at all. Instead, use the HasData property of the subreport. The basic idea is explained in <post#=311977>post 311977</post#>. See if you can adapt that for your own use. Don't hesitate to post back if you need more assistance.

  9. #9
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Subtotals in Report (Access 2002 SP2)

    I used the humongous method and got my total, so that's a relief. I appreciate your help with that.

    I am working on the HasData now, but I'm getting a name error. This is what I'm using: =IIf([Total of Fees].invoice.HasData,Sum([total fees]),0)

    Total of Fees is what shows up in my object selected area of the toolbar when the subreport is clicked. Invoice is the name of the report. The total in the footer of the subreport is actually named "sum fees", but using that is place of the sum([total fees]) also gives me a name error. Do you have any idea what else I should check?

    TIA
    egghead

  10. #10
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Subtotals in Report (Access 2002 SP2)

    I got an invalid syntax error when I tried this. It goes in a text box above the subreport, correct?
    egghead

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

    Re: Combining Subtotals in Report (Access 2002 SP2)

    Edited by HansV to correct expression - changed semi-colons to commas..

    You should use

    =IIf([Total of Fees].Report.HasData,[Total of Fees]![Sum Fees],0)

    The use of Report means that you refer to the subreport as a report, not as a control on the main report.

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

    Re: Combining Subtotals in Report (Access 2002 SP2)

    Sorry, my bad. I forgot about localization (again). The expression should contain commas instead of semi-colons. I have corrected my reply.
    The expression should be the Control Source of a text box on the main report, for example above the subreport.

  13. #13
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Subtotals in Report (Access 2002 SP2)

    Eureka!!

    I got all three working. Thanks very much! I learned so much... <img src=/S/clever.gif border=0 alt=clever width=15 height=15>
    egghead

Posting Permissions

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