Report Totals on Text Boxes in Sub Report

  • Thread starter Thread starter richard
  • Start date Start date
R

richard

Hi

I have a report with sub reports which are hidden. I reference text boxes
from the sub reports as follows
rptReportName.Report!textboxname
I would like to Sum() these text boxed on the main report in the report
footer, but I only get error. I have tried the text box name as follows

=Sum(textboxname)
I have also tried

=sum(rptReportName.Report!textboxname)

but when I run the report access prompts me values for the fields

Can anyone point me in the direction I might need

thanks

Richard
 
Use a Running Sum text box on the main report to accumulate the subtotals
from each instance of the subreport.

1. Open the subreport in design view, and in its Report Footer section of
your subreport, add a text box with these properties:
Control Source =IIf([Report].[HasData], Sum([Amount]), 0)
Format Currency (or General Number if you
prefer.)
Name txtSubTotal
Replace Amount with your field name. Leave Report exactly as is. The Format
property is to ensure Access understands the value as numeric. The Name can
be anything valid (but not the same a a field name.) Be sure to use the
Report Footer section (not the Page Footer.) Save. Close.

2. Open the main report in design view. In the same section as the
subreport, add a text box with these properties:
Control Source =[Sub1].[Report]![txtSubTotal]
Running Sum Over All
Format Currency
Visible No
Name txtSubTotalRS
Replace Sub1 with the name of your subreport control (which may not be the
same as the name of the report it holds - its SourceObject.) Leave Report
the same. Use a numeric format. You can leave visible for now if you wish.
Any valid name will do.

3. In the Report Footer section of the main report, add a text box with
properties:
Control Source =[txtSubTotalRS]
Format Currency

More information about the need to test HasData in the subreport:
http://allenbrowne.com/RecordCountError.html
 
As ever the simple answer is the best, thanks for the help

Richard

Allen Browne said:
Use a Running Sum text box on the main report to accumulate the subtotals
from each instance of the subreport.

1. Open the subreport in design view, and in its Report Footer section of
your subreport, add a text box with these properties:
Control Source =IIf([Report].[HasData], Sum([Amount]), 0)
Format Currency (or General Number if you
prefer.)
Name txtSubTotal
Replace Amount with your field name. Leave Report exactly as is. The Format
property is to ensure Access understands the value as numeric. The Name can
be anything valid (but not the same a a field name.) Be sure to use the
Report Footer section (not the Page Footer.) Save. Close.

2. Open the main report in design view. In the same section as the
subreport, add a text box with these properties:
Control Source =[Sub1].[Report]![txtSubTotal]
Running Sum Over All
Format Currency
Visible No
Name txtSubTotalRS
Replace Sub1 with the name of your subreport control (which may not be the
same as the name of the report it holds - its SourceObject.) Leave Report
the same. Use a numeric format. You can leave visible for now if you wish.
Any valid name will do.

3. In the Report Footer section of the main report, add a text box with
properties:
Control Source =[txtSubTotalRS]
Format Currency

More information about the need to test HasData in the subreport:
http://allenbrowne.com/RecordCountError.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

richard said:
I have a report with sub reports which are hidden. I reference text boxes
from the sub reports as follows
rptReportName.Report!textboxname
I would like to Sum() these text boxed on the main report in the report
footer, but I only get error. I have tried the text box name as follows

=Sum(textboxname)
I have also tried

=sum(rptReportName.Report!textboxname)

but when I run the report access prompts me values for the fields

Can anyone point me in the direction I might need

thanks

Richard
Click to expand...
Click to expand...
 
Back
Top