Carry Subtotal to Grand Total

[rmcompute]

I created a subtotal by Serial number that creates the following in the
Serial Number Footer:

Name: SumCopies
Control Source: =Max([PRESENTRRev])-Min([PRESENTRRev])

In the Report Footer I created the following:

Name: SumSumCopies
Control Source: Sum([SumCopies])

The system does not recognize SumCopies in the Report Footer and treats it
like a parameter. I cannot move the formula to the SumSumCopies field since
the max and min calculation should only be done for a particular serial
number. Is there a way to fix this problem ?

 

[Allen Browne]

As you found, you can't sum a calculated control like that.
Instead, use a Running Sum control to collect the total.

1. In the same section as the SumCopies, add another text box and set these
properties:
Name SumCopiesRS
Control Source =[SumCopies]
Running Sum Over All
Format General Number
Visible No
This hidden text box aggregates the total for you.

2. In the Report Footer, add a text box and set its Control Source to:
=[SumCopiesRS]

 

[rmcompute]

Worked Like a Charm. Thank you!

As you found, you can't sum a calculated control like that.
Instead, use a Running Sum control to collect the total.

1. In the same section as the SumCopies, add another text box and set these
properties:
Name SumCopiesRS
Control Source =[SumCopies]
Running Sum Over All
Format General Number
Visible No
This hidden text box aggregates the total for you.

2. In the Report Footer, add a text box and set its Control Source to:
=[SumCopiesRS]

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

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

I created a subtotal by Serial number that creates the following in the
Serial Number Footer:

Name: SumCopies
Control Source: =Max([PRESENTRRev])-Min([PRESENTRRev])

In the Report Footer I created the following:

Name: SumSumCopies
Control Source: Sum([SumCopies])

The system does not recognize SumCopies in the Report Footer and treats it
like a parameter. I cannot move the formula to the SumSumCopies field
since
the max and min calculation should only be done for a particular serial
number. Is there a way to fix this problem ?

 

[Marshall Barton]

I created a subtotal by Serial number that creates the following in the
Serial Number Footer:

Name: SumCopies
Control Source: =Max([PRESENTRRev])-Min([PRESENTRRev])

In the Report Footer I created the following:

Name: SumSumCopies
Control Source: Sum([SumCopies])

The system does not recognize SumCopies in the Report Footer and treats it
like a parameter. I cannot move the formula to the SumSumCopies field since
the max and min calculation should only be done for a particular serial
number. Is there a way to fix this problem ?

Add a hidden text box named txtRunTotal to the group footer.
Set its control soutce to =SumCopies and its RunningSum
property to Over All.

Then the report footer text box can use the expression:
=txtRunTotal