Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Refer to a control in a sub report Access 97

    I have a report with a sub report in the details section of the main report that provides detail of items and the quantity. I would like to refer to the Quantity control of the sub report for a total. I tried =Sum([Report].[srptAmazonOrderDetails].[Quantity]) in the main report header but on opening the report I am asked for a value. If I place the expression in the page footer I am not asked for a value but it returns an error. I prefer the result to appear in the report header but I could add another step if I could get an accurate return in the page footer. What am I doing wrong?
    Thanks
    chuck

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I believe your issues relates to the structure of your expression - and it's a common problem developers run into. Per KB 113352 the syntax should be
    Reports![main report name]![subreport control name].Report![control name]
    If that syntax doesn't work for you, post back and we will try to help.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks Wendell. I had tried that syntax. =Sum([Reports]![rptAmazonOrders]![srptAmazonOrderDetails].[Report]![Quantity]) I don't get an error but I get nothing. If I drop the "Sum" I get a result of the first record which happens to be 1. The expression also returns 1 if I drop the name of the main report. The expression builder does not add the name of the main report but like the article you referred me to, the help file adds the main report name so I had tried that.
    Thanks
    chuck

  4. #4
    New Lounger
    Join Date
    Mar 2013
    Posts
    16
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by chuckrau View Post
    Thanks Wendell. I had tried that syntax. =Sum([Reports]![rptAmazonOrders]![srptAmazonOrderDetails].[Report]![Quantity]) I don't get an error but I get nothing. If I drop the "Sum" I get a result of the first record which happens to be 1. The expression also returns 1 if I drop the name of the main report. The expression builder does not add the name of the main report but like the article you referred me to, the help file adds the main report name so I had tried that.
    You need to put the control with the SUM in the subform, possibly hidden (or visible) in a section footer. Then, in the main form, you just refer to the control as Wendell mentioned. You cannot SUM the values in the subform from the main form. All you can do is reference the SUM that the subform performs.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post
    OK James, we are getting closer. I now get a return but no a correct return. On the sub report, when viewed by itself, the total is correct, but the total is not correct when viewed in the report. At least the syntax part of the problem is corrected and I thank you guys for that. I am sure it is something simple...it always is with me.
    Last edited by chuckrau; 2013-03-14 at 13:49.
    Thanks
    chuck

  6. #6
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post
    We jumped a few hoops but got it. In the sub report I placed a text box named [UnitSum] in the details section with the expression =Sum([Quantity]). In the main report I placed a text box in the details section with the expression =[Reports]![rptAmazonorders]![srptAmazonOrderDetails].[Report]![UnitSum] with the running sum property set to over group. Then I referred to that text box in my date footer and returned the correct total.

    Thanks for helping.
    Thanks
    chuck

Posting Permissions

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