Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using data on a subrpt in a calculated control (AccessXP)

    I am working on a report that involves a subreport. From the subreport I want to sum the control PaymentAmount with the control to show the results group header on the main report. Using the expression builder I entered this in the control =Sum(subrptPayments.Report!PaymentAmount).

    Thanks for the help. Fay

  2. #2
    Lounger
    Join Date
    Jan 2001
    Location
    Swindon, Wiltshire, United Kingdom
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using data on a subrpt in a calculated control (AccessXP)

    Off the top of my head you may not get what you expect because of the order in which the infromation in a sub report/report is generated.

    You could try using a domain aggregate function (DSum) that totals the related records using the link master field e.g. in the text box type (all on one line) something like

    =DSum("[EnterSubFormTableNameHere]","[PaymentAmount]","[EnterLinkChildFieldNameHere] = " & [EnterLinkMasterFieldNameHere])

    This assumes that the linkmasterfield is a numeric field

    if it is a text field then you may need something like...

    =DSum("[EnterSubFormTableNameHere]","[PaymentAmount]","[EnterLinkChildFieldNameHere] = ' " & [EnterLinkMasterFieldNameHere] & "' ")

    notice that there are no spaces between the ", the ' and the second "

    they are included here only for clarity.

    Let me know if this helps...

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Using data on a subrpt in a calculated control (AccessXP)

    Another approach is to put a control in Report footer of the subreport.
    This can be set to = sum([Paymentamount]), and name it something, ctrSumofPayments

    If this seems to work OK, you can transfer the result back to your main report.

    In the control you previously created , use the expression builder again to set the control source to be the control in the footer.

    You can't refer to the subreport directly, because it is open within the main report. You will find that there is a + next to the report name, and double clicking that will list any subreports. click the subreport, and you should see the total field.

    If all is OK, then finally shrink the subreport footer and its control so you don't see them.
    Regards
    John



  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using data on a subrpt in a calculated control (AccessXP)

    Okay I got the first half set with a control called SumofPayment on the subrpt. Using the Expression builder it gave me this for the control source for the textbox located in the =Sum(subrptPayments.Report!SumofPayments) control source. But it doesn't work. Any ideas?

    Fay

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

    Re: Using data on a subrpt in a calculated control (AccessXP)

    The control SumOfPayments in the report footer of the subreport should already sum the payments in the subreport; its control source should be something like =Sum([Payment]).

    If this is correct, the textbox in the main report only needs to refer to the SumOfPayments control:
    =[subrptPayments]![SumOfPayments]
    You shouldn't use a Sum in the control source of this text box.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Using data on a subrpt in a calculated control (AccessXP)

    Hans has answered your questions.

    In case it is still not clear I attach a small sample.

    The control source of the control in the main report is:
    =[rptPaymentssub].Report!ctrSumofPayments

    One thing you need to be careful about. When you place a subreport in a report, the subreport is displayed within a control on the main report. The name of that control may not be the same as the name of the subreport it contains.
    When you refer to things within the subreport, you refer to them by the name of the subreport control rather than the name of the subreport.
    (In the example I attach both names are the same so this problem does not matter)
    Attached Files Attached Files
    Regards
    John



Posting Permissions

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