Sum a calculated field in Microsoft Access 1

[nicolenj]

Report Desired Result:

Group Rate Round
1 498.17 Y
1 498.17 Y
1 498.17 Y
1 498.17 Y
1 498.17 Y
1 498.17 Y
Total Group 1 : $2989.00 (I used the following control source: =IIf([Round]=Yes,Round(Sum([Rate]),0),Sum([Rate]))

2 514.30 N
2 514.30 N
Total Group 2 : $1028.60(I used the following control source: =IIf([Round]=Yes,Round(Sum([Rate]),0),Sum([Rate]))

Grand Total : $4017.60 <------this is what I want to appear but when I use the same control source as above, I get $4018 as the results.

How can I get the Grand Total (in the Report footer) to add up the calculated results ($2989.00 plus $1028.60).

Please HELP!

 

[dhookom]

I believe since you are rounding the Sum() at the group level rather than the value at the detail level, you will need to use a Running Sum on a text box in the group footer.

Add a text box in the group footer:
[tt]
Name: txtRunSum
Control Source: =Abs([Round]=True)*Round(Sum([Rate]),0)+Abs([Round]=False)*Sum([Rate])
Visible: No
[/tt]
Then add a text box to the report footer section:
[tt]
Name: txtRunSumTotal
Control Source: =txtRunSum
Visible: Yes
[/tt]


Duane
Hook'D on Access
MS Access MVP

 

[nicolenj]

Thank you for the quick response!! Do I need to change the Running Sum property for either the group footer or the report footer?

 

[nicolenj]

I changed the Running Sum property for the text box in the group footer to Yes and it worked!!! Thank you! Thank you!

 

[dhookom]

Good catch. I forgot to post the Running Sum property which needed to be set to Over All.


Duane
Hook'D on Access
MS Access MVP