creating a total field in the grouping footer of a report

[RBE]

I have a slight problem. I am trying to create a total field in the grouping footer of a report. Here is the problem. I am trying to total a calculated field (the control source is an expression not a field name). I have tryed the =Sum([fieldname]) but that does not work,(it will not recognize the field in the details section) as well as the dsum, but I have no cirtera and I don't know how to call the detail section of the report from the footer. It is calculating an unbound text box with an expression in the control source. Any suggestions on how I can total this up. I can't use the expression in a query because it is calculating off of a running total field in the report and I can't seem to get a running total to calculate in a query consistantly. Any Suggestions?
RBE RBE


Are you 100% sure of where you are going.

 

[tempclerk]

Someone stated in another thread that you can't sum a calculated control, but you can sum the calculation itself. Try putting the same calculation in place of the fieldname.

 

[turtlemaster]

that will work perfectly.

=sum([field1]*[field2]/3) for example should work the way you want.

 

[JerryDennison]

You cannot "address" a calculated control in the detail section of your report in the header or footer directly. You can duplicate the formula in the header or footer as an aggregate. For example, if in the detail section you have an unbound control called txtLineTotal with the following formula:

=[ItemQty]*[ItemAmount]

and you want a total of txtLineTotal in the group footer you would use the following formula:

=Sum([ItemQty]*[ItemAmount])

 

[RBE]

I just wanted to let you all know that I did find a way to sum a calculated control in the group footer of a report. This is how I did it. It may help others as well so I am posting it here.

1. create an unbound textbox in the detail section of the report.
2. Put this expression in the control source: =[field]([field] is the field you want to total)
3. change the running total control on the newly created textbox to "over group"
4. In the group footer create an unbound text box.
5. In the control source put =[field2] where field2 is the newly created textbox in the detail.

This worked for me. To total over the entire thing all you need to do is create another textbox in the detail and have it total overall and then one in the report footer referencing it.

I hope this helps some one other then me.
Thanks for all your help anyway guys....
RBE RBE


Are you 100% sure of where you are going.