How do I get a report total from 2 sub reports

[Tasca]

Hi

I have a report for which I want some running totals so I created 3 sub reports with their own running totals and put them all on a report. This works fine but I want to also have a total of the first 2 sub report totals. I tried to use the expression builder and it seemed to work but when I opened up the report it asked me for parameter values for the 2 totals.

The 3 reports are based on the same query with different filters so I renamed the total on each report and am trying to add these totals

 

[dhookom]

What expression did you try?
Are all of your subreports and totals in the same section of the main report?
What are your subreport control names as well as the names of the text box controls on the subreports?

Have you considered creating totals queries similar to your subreport record sources and including these queries in your main report's record source?

Duane
Hook'D on Access
MS Access MVP

 

[Tasca]

I have three sub reports with their total in their report footer. The first sub-report contains the initial costs of the project, the second sub-report has definite additional costs and the third sub-report has possible additions. What I am trying to do is add the first and second totals to give a new total cost.

The sub-reports (for simplicity) are called Report Pt1, Report Pt2 and Report Pt3. I have named the total for each sub-report "SubTot".

All sub-reports are in the detail part of the main report

The expression I used was =Sum(Reports![Report Pt1]!SubTot+[SubTot])

I tried to put this in the report footer of Sub-report 2 and also in the main report after sub-report 2

Not sure how the queries part would work? Create a 4th query with the filters for the first 2 queries and add a 4th sub report with just the total?

 

[dhookom]

A subreport is a control on the main report. This control has a name which generally defaults to the name of the subreport. Assuming the names of your two subreport controls are SubReport1 and SubReport2 and they each have a text box in their report footers named txtSubTot. Your text box in the same section of your main report would have a control source of:

  =SubReport1.Report.txtSubTot + SubReport2.Report.txtSubTot

If there is a chance that either of the reports might not return any records you would need to use:

  =IIf(SubReport1.Report.HasData, SubReport1.Report.txtSubTot,0) + IIf(SubReport2.Report.HasData, SubReport2.Report.txtSubTot,0)

Duane
Hook'D on Access
MS Access MVP

 

[Tasca]

Hmmmm, ok I tried

=IIf([Report 102 Pt1].Report.HasData,[Report 102 Pt1].Report.SubTot,0)+IIf([Report 102 Pt2].Report.HasData,[Report 102 Pt2].Report.SubTot,0)

below Report 102 pt2 in the main report and got the #Name? error...

 

[dhookom]

Did you try each subreport reference separately to see if either works?

Are both subreports in the same report section as your text box?

Are the subreport controls on the main report named "Report 102 Pt1" and "Report 102 Pt2"?

Are both text boxes in the report footers of the subreports and are they both named "txtSubTot"?

Duane
Hook'D on Access
MS Access MVP

 

[Tasca]

THATS the one. Thanks very much for that!

I inserted the subreport into the main report then copied it down twice and changed the source so while it LOOKED like I had done it correctly the subreports where actually called child5 and child6 not report 102 pt1 and subreport 102 pt2. It now works fine.