INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

In thread703-1030548 DHookom wrote:

In thread703-1030548 DHookom wrote:

(OP)
In thread703-1030548: =Sum not working DHookom wrote: QUOTE: Captain Awesome, Is [Confirmed Total] a field in your report's record source? If it is a control name then you can't "Sum()" it. You can only Sum() expressions based on fields in your report's record source.

There are other ways to get the sum including summing your control source from the control or using a running sum. END QUOTE

I have the field named <InterestWI>. In the details of my subreport, which is based on a query, I want to sum that field, ([InterestWI],)in the details section of my report. Both the Name and the Control source are named InterestWI.

That field works properly in the Details section of the report. I.e. the field shows the interest that corresponds to InterestWI for each record.

However, the text box in the footer section has the formula =Sum([Interest]) In printview, that control returns #Error.

I have tried changing the name of the control source which resulted in no change.
I have tried =Sum(Nz([InterestWI]) and that too returns #Error.

What am I doing wrong? Thank you.

rccline

RE: In thread703-1030548 DHookom wrote:

if you want to show a sum of a field in the main report of records in the subform.
1) Sum up the records in the subform as you describe. Assume that control on your subform is called "txtSum"
2) on the main report you then make a calculated control
= [NameOfYourSubformControl].report![txtSum]
if this was a form not a report replace the .report with .form.

Note a subform control is the container that holds a report or form. Its name is not necessarily the name of the subform or report.

RE: In thread703-1030548 DHookom wrote:

(OP)
MajP:

I haven't even gotten to the main report yet. I can't get the textbox in the footer to work.
Plesae look at the attachment. Atached is what I have. This returns #ERROR

  • RE: In thread703-1030548 DHookom wrote:

    Can you put it in the Report Footer instead of page footer? In the page footer you will get an error, but I do not remember the reason for that.

    RE: In thread703-1030548 DHookom wrote:

    If you do want to show the overall total but on each page you can do the following.
    Create a control in another section of your report that performs the calculation, and set its visible property to false. Then create another unbound text box in the page footer. Enter the name of the control containing the calculation as the text boxes controlSource property setting.

    This will still not give you the total per page but the overall total. I googled the reason you cannot put it in the page footer, and I could not find it. It just is.

    RE: In thread703-1030548 DHookom wrote:

    (OP)
    Placing the text box in the report footer instead of the page footer worked.

    My main report is returning the Error #Name?

    =([subRptGrantees].[Report]![txtGranteeWI])

    =([Name of SubReport].[Report]![Name of text Box on subReport Footer])

    What am I doing wrong here?

    rccline

    RE: In thread703-1030548 DHookom wrote:

    Not sure if it would make a difference but I would lose the outside parentheses

    =[subRptGrantees].[Report]![txtGranteeWI]

    1. Ensure that "subrptGrantees" is the name of the subform control, which may not be the same as the report within the control. You have to click on the ouside not inside.
    2. It is possible that the object inside your subform is a form and not a report. If you used a form then replace the word "report" with "form" as in .[form}!.

    I hate calcuated controls because any typo or slightly wrong syntax you do not know what is going on. It just won't work.

    RE: In thread703-1030548 DHookom wrote:

    (OP)
    Thank you MajP. I have used the format you suggest and I still get the error message. #Error.

    The calculation appears below my subreport on the main report. I have the numbers there. The downside is, I can't place them where I want them in the report.

    rccline

    RE: In thread703-1030548 DHookom wrote:

    Can you post exactly what you tried. Any little typo can cause this to error. Did you verify the name of the subform control?

    RE: In thread703-1030548 DHookom wrote:

    (OP)
    Thank you for taking a look at this MajP.

    My first record displays 0.00 in the field WI, which the text box txtGranteeWI references. There are no fields in that record which have any data. That first record returns #Error. That was the problem. If I put some data in one of the fields of that record, the number 0.00 displayed. So, the error is coming from a record which has no data. I can place some data in a field that does not show in my report. That gets rid of the #Error. At least I have a solution for this instance. Is there a better solution that isn't too complicated?

    CODE

    =[subRptGrantees].[Report]![txtGranteeWI] 

    RE: In thread703-1030548 DHookom wrote:

    To make sure I understand. Currently if the subform has records the correct sum is reported in the subform footer and now on the main form? If no records are in the subform then you would want it to show 0?

    RE: In thread703-1030548 DHookom wrote:

    The image shared shows the total text box in the Page Footer. Totals do not work in Page Footers. Also, if this is actually a subreport, it is important to understand that Page sections do not display in subreports.

    If we are talking about reports and not forms, can we stop using "form" since my brain is a bit OCD.

    Duane
    Vevey, Switzerland
    Hook'D on Access
    MS Access MVP 2001-2016

    Red Flag This Post

    Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

    Red Flag Submitted

    Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
    The Tek-Tips staff will check this out and take appropriate action.

    Reply To This Thread

    Posting in the Tek-Tips forums is a member-only feature.

    Click Here to join Tek-Tips and talk with other members!

    Resources

    Close Box

    Join Tek-Tips® Today!

    Join your peers on the Internet's largest technical computer professional community.
    It's easy to join and it's free.

    Here's Why Members Love Tek-Tips Forums:

    Register now while it's still free!

    Already a member? Close this window and log in.

    Join Us             Close