Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula in Report (Access 2000)

    Hi,

    I have a main report and a subreport. In the subreport I have a details section and then a details footer with a Sum formula for some of the columns in the details section. I would like to add on the main report a grand total of the numbers included in the subreport. I have used the following formula: =Sum([RptSubAdLog].[Report]![Company_Pledge]). The RptSubAdLog is the subreport and the Company Pledge is one of the controls in the subreport that I want a grand total of in the Main report footer. This is not working. No numbers or errors come up at all. Any suggestions. Thank you.

    Dorothy

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

    Re: Formula in Report (Access 2000)

    In general, you can't use Sum to add the contents of controls, only to add the values of fields and expressions. Is the subreport repeated on the main report? In that case, you will have to find another way to calculate the sum, perhaps with DSum. If the subreport is displayed only once, you can calculate the sum in the report footer of the subreport, and refer to that in the main report.

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula in Report (Access 2000)

    Hi Hans,

    I am incredibly lost and have tried a number of things. Can you explain the Dsum and how I would use it? These are the elements involved:
    A subreport name RptSubAdLog
    This subreport has detail section with Company_Pledge (currency)
    The subreport has a detail footer that sums the Company Pledge: =Sum[Company_Pledge]
    There are multiple companies listed in the subreport

    Can I reference the =Sum[Company_Pledge] box on the main report footer? if so, as I am really lost, how would I do this specifically? From your last post it sounds like this is not possible which brings us to Dsum. Again, I find help to be no help but it may be because I am so ignorant in this area.

    Thanks for any help you can give me.

    Dorothy

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

    Re: Formula in Report (Access 2000)

    1. If you open the main report, are multiple copies of the subreport displayed, or just one? (If just one, this single copy can still display multiple companies within the subreport!)

    2. How is the subreport linked to the main report? To check this, open the main report in design view, and click ONCE on the subreport to select it. Activate the Data tab of the Properties window. What are the Link Master Fields and Link Child Fields properties?

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula in Report (Access 2000)

    Hi Hans,

    When I open the report in design view there is just one subreport. In the regular view the subreport displays once under a Lead Company, which is part of the Main report, but there are many Lead companies, each with a subreport listed with company info. The subreport is link as follows: Link child fields - Lead_Name. Link Master fields- Lead_Name.

    Dorothy

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

    Re: Formula in Report (Access 2000)

    Thanks for this description. MSKB article ACC2000: How to Display and Total Subtotals from Subreports describes several methods you can use. Perhaps the easiest is the method described under "Using a Second Subreport". In your situation, it would go like this:

    - Make sure the report is closed.
    - Open the RptSubAdLog (sub)report in design view.
    - Select File | Save As... to make a copy. Name the copy RptSubAdLogSum.
    - Delete all controls from the Detail section, then set the height of the Detail section to 0.
    - Close and save RptSubAdLogSum.
    - Open the main report in design view.
    - Add RptSubAdLogSum as a subreport to the report footer section of the main report.
    - Make sure that this subreport is NOT linked to the main report: the Link Child Fields and Link Master Fields properties must be blank.

    This method will not work correctly if you open the main report from a form with an additional where-condition imposed, but it should be OK otherwise. Post back if you have problems.

  7. #7
    Star Lounger
    Join Date
    Mar 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula in Report (Access 2000)

    Hi Hans,

    IT WORKED!!! Thank you so much for your help. I did not imagine when I set up this report using a subreport it would take much more than a simple formula to get grand totals. As usual, a very good learning experience. Take care and probably will be seeing me again soon.

    Dorothy

Posting Permissions

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