In thread703-1030548 DHookom wrote:

[rccline]

In thread703-1030548 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

 

[MajP]

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.

 

[rccline]

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

[li][/li]

 

[rccline]

Looks like the Attachment didn't work. Please find the attached jpg image of my subform attached.

Thank you.

rccline

 

[MajP]

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.

 

[MajP]

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.

 

[rccline]

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

 

[MajP]

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.

 

[rccline]

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

 

[MajP]

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

 

[rccline]

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?

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

 

[MajP]

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?

 

[dhookom]

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