Group Total of Calculated Field with Running Sum in Group Header - S.O.S. (((

[powerniso]

Hi everyone!
And please help.

This is my report. The red circle below is the calculated field ([TotalDaysStayed]) with running sum. It shows up the correct number as sums the last column ([DaysStayed]).
The field [DaysStayed] is calculated as: [DateLast]-[DateFirst]+1 (interval including both start and end dates)
But, ... when I try to show up the total (textbox named - [GTotalOfDays]) of the last column at the group header (the blue circles), it shows up the sum of the first two group headers.
I tried :
- [GTotalOfDays] control source = [TotalDaysStayed] -> Result: [GTotalOfDays]= first value of the [TotalDaysStayed] which is 7 (not correct, must show 15)
- [GTotalOfDays] Running Sum property set to Over Group, control source = [TotalDaysStayed] -> Result: as on image

How can I sum up the [DaysStayed] field in Group Header for a group?
Thanks thanks and again thanks if you try to help me.

 

[dhookom]

If you are looking for only group totals, you can try a control source of:

=Sum([DateLast]-[DateFirst]+1)

Duane
Hook'D on Access
MS Access MVP

 

[powerniso]

Thanks for your reply,

But, I forgot to mention that:

1st - the fields [DateFirst] and [DateLast] are both calculated fields
2nd - The problem occurs when adding the total field to Group Header, if I add the Total Field to group footer, there is no problem.

Thanks.

 

[dhookom]

Whatever the calculation of the fields might be, you should be able to use =Sum([ your expression here ]) in group and report headers and footers. This will not work in page sections.

Duane
Hook'D on Access
MS Access MVP

 

[powerniso]

Oops, "SUM" function not works on header and footer on calculated fields (unbound - bound to function rather than a field on a table or query). But I am not interested on using functions, but why group total shows correct on footer, but wrong on header. Any help appreciated.

 

[dhookom]

What is in the control source in the group header? You can't reference controls from other sections.

Duane
Hook'D on Access
MS Access MVP