Taking sum of a field from sub report

[John]

Hi

On a sub report I have a field datetot which is the sum of two fields those
two fields are in turn coming from two further sub reports of the sub
report. How can I take the sum of datetot field to the main report?

Thanks

Regards

 

[Allen Browne]

See:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html

 

[John]

Thanks for that. My problem has one more level in that the field 'Amount' on
sub report is defined as =[SumA] + [SumB] where SumA and SumB are themselves
coming from sub reports on the sub report. Hopefully the diagram below
explains it;

Report
|-------------Sub Report
| Amount =[SumA] + [SumB]
|
| SumAmount = sum([Amount]) gives #Error
|
|--------- Sub Sub Report A
| SumA = Sum([A])
|
|
|--------- Sub Sub Report B
SumB = Sum()


When on sub report I try to do =Sum[Amount] I get #error. Any solution to
that?

Thanks

Regards

 

[Allen Browne]

So you have the Amount field working in the main subreport, but when you try
to sum this Amount field in the Report Footer section of the main subreport,
it doesn't work?

Use a running sum text box to collect the total.
It's properties might look like this:
Control Source =[Amount]
Running Sum Over All
Format Currency
Name txtAmountRS
Visible No

Now in the Report Footer section of the main subreport, add another text box
with Control Source of:
=[txtAmountRS]

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

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

Thanks for that. My problem has one more level in that the field 'Amount'
on sub report is defined as =[SumA] + [SumB] where SumA and SumB are
themselves coming from sub reports on the sub report. Hopefully the
diagram below explains it;

Report
|-------------Sub Report
| Amount =[SumA] + [SumB]
|
| SumAmount = sum([Amount]) gives #Error
|
|--------- Sub Sub Report A
| SumA = Sum([A])
|
|
|--------- Sub Sub Report B
SumB = Sum()


When on sub report I try to do =Sum[Amount] I get #error. Any solution to
that?

Thanks

Regards

See:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html

 

[John]

Thanks. The running total works when sub form is run on its own but not as
part of the main report.

Regards

So you have the Amount field working in the main subreport, but when you
try to sum this Amount field in the Report Footer section of the main
subreport, it doesn't work?

Use a running sum text box to collect the total.
It's properties might look like this:
Control Source =[Amount]
Running Sum Over All
Format Currency
Name txtAmountRS
Visible No

Now in the Report Footer section of the main subreport, add another text
box with Control Source of:
=[txtAmountRS]

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

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

Thanks for that. My problem has one more level in that the field 'Amount'
on sub report is defined as =[SumA] + [SumB] where SumA and SumB are
themselves coming from sub reports on the sub report. Hopefully the
diagram below explains it;

Report
|-------------Sub Report
| Amount =[SumA] + [SumB]
|
| SumAmount = sum([Amount]) gives #Error
|
|--------- Sub Sub Report A
| SumA = Sum([A])
|
|
|--------- Sub Sub Report B
SumB = Sum()


When on sub report I try to do =Sum[Amount] I get #error. Any solution to
that?

Thanks

Regards

See:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html


On a sub report I have a field datetot which is the sum of two fields
those two fields are in turn coming from two further sub reports of the
sub report. How can I take the sum of datetot field to the main report?

 

[Allen Browne]

If the main report has records of its own, the subreport "exists" in
multiple separate instances, each indepenent of the others. You will not be
able to accumulate the subreports totals across its multiple instances.

If that's the problem you are seeing, you will need to pass back the
accumulated total from the subreport onto the main report. You can then use
another text box to accumulate the subtotals into a grand total on the main
report.

(Hope this approach works for you: there can be timing issues between when
the main report reads the subreport's subtotal and when the subreport has
completed accumulating the values.)

An alternative approach might be to just use a DSum() expression in the main
report's Report Footer section, though this becomes more difficult if the
report could be filtered (especially since Access doesn't maintain its
FilterOn property reliably.)

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

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

Thanks. The running total works when sub form is run on its own but not as
part of the main report.

Regards

So you have the Amount field working in the main subreport, but when you
try to sum this Amount field in the Report Footer section of the main
subreport, it doesn't work?

Use a running sum text box to collect the total.
It's properties might look like this:
Control Source =[Amount]
Running Sum Over All
Format Currency
Name txtAmountRS
Visible No

Now in the Report Footer section of the main subreport, add another text
box with Control Source of:
=[txtAmountRS]

Thanks for that. My problem has one more level in that the field
'Amount' on sub report is defined as =[SumA] + [SumB] where SumA and
SumB are themselves coming from sub reports on the sub report. Hopefully
the diagram below explains it;

Report
|-------------Sub Report
| Amount =[SumA] + [SumB]
|
| SumAmount = sum([Amount]) gives #Error
|
|--------- Sub Sub Report A
| SumA = Sum([A])
|
|
|--------- Sub Sub Report B
SumB = Sum()


When on sub report I try to do =Sum[Amount] I get #error. Any solution
to that?

Thanks

Regards

See:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html


On a sub report I have a field datetot which is the sum of two fields
those two fields are in turn coming from two further sub reports of
the sub report. How can I take the sum of datetot field to the main
report?