Summing expressions per group in a report

Running Sum generates a "running sum" with a value for each detail line. It
is not for totals in footers, so set it to "No" for your Text Box

The Sum function creates a sum and is intelligent enough to limit that sum
to detail lines that are in the group of the Group Footer. Try setting the
Control Source of the Text Box in the Group Footer to the expression you
list, inside a Sum function:

= Sum(IIf(Now()>[Begin Date],((Month([End Date])-Month([Begin
Date])+1)-(Month(Now())-Month([Begin Date])))*[Monthly Amount],[Inv
Amount]))

Generally, if you repeat the expression used for Calculated Controls inside
the Sum, it will work... but it IS possible, sometimes, to have an
expression complex enough to confuse the issue and give.

As an alternative, you may be able to create a Text Box in detail with
Running Sum set to Over Group and just refer to it in the footer, without
the Sum, as

= Reports!NameOfYourReport!NameOfYourCalculated Control

Running Sum of the Text Box in the Footer should be "No".

This should display the value of the Calculated Control from the last detail
line, which, of course, would be the Sum you seek.

If you don't want that running sum to appear in the detail lines, set the
Visible property of the Calculated Control to "No".

Larry Linson
Microsoft Office Access MVP

<Lex Short> wrote in message news:20091014153414rozee415@yahoo.com...
>I have unsuccessfully tried to create a group subtotal in a report. I need
>the report to add together the amounts in only that group.
>
> I have the following expression in the report:
> =IIf(Now()>[Begin Date],((Month([End Date])-Month([Begin
> Date])+1)-(Month(Now())-Month([Begin Date])))*[Monthly Amount],[Inv
> Amount])
>
> In the group footer I have a text box that says
> source: same formula as listed above
> Running sum: Over Group
>
> The result is a running total for the whole report instead of per group.
>
>
> If I change it to
> Running sum: No
>
> Then the result is the amount of the last record on the page.
>
> How can I get the report to show the subtotal for that expression per
> group?
>
> Many thanks for your help!
>
> EggHeadCafe - Software Developer Portal of Choice
> Secure Session State Transfer: ASP to ASP.NET
> http://www.eggheadcafe.com/tutorials/aspnet/19f8d04a-6bce-4ee0-8d6e-1e...cbb92e1

Larry, you are a life saver! Thank you so much for your help. The
first suggestion you gave worked beautifully. Adding =Sum before the
expression in the Control Source works. I was at a loss since I
didn't have a field to reference. Thanks for your quick response!


On Oct 14, 3:05 pm, "Larry Linson" <boun... RemoveThis @localhost.not> wrote:
> Running Sum generates a "running sum" with a value for each detail line. It
> is not for totals in footers, so set it to "No" for your Text Box
>
> The Sum function creates a sum and is intelligent enough to limit that sum
> to detail lines that are in the group of the Group Footer. Try setting the
> Control Source of the Text Box in the Group Footer to the expression you
> list, inside a Sum function:
>
>  = Sum(IIf(Now()>[Begin Date],((Month([End Date])-Month([Begin
> Date])+1)-(Month(Now())-Month([Begin Date])))*[Monthly Amount],[Inv
> Amount]))
>
> Generally, if you repeat the expression used for Calculated Controls inside
> the Sum, it will work... but it IS possible, sometimes, to have an
> expression complex enough to confuse the issue and give.
>
> As an alternative, you may be able to create a Text Box in detail with
> Running Sum set to Over Group and just refer to it in the footer, without
> the Sum, as
>
>  = Reports!NameOfYourReport!NameOfYourCalculated Control
>
> Running Sum of the Text Box in the Footer should be "No".
>
> This should display the value of the Calculated Control from the last detail
> line, which, of course, would be the Sum you seek.
>
> If you don't want that running sum to appear in the detail lines, set the
> Visible property of the Calculated Control to "No".
>
>  Larry Linson
>  Microsoft Office Access MVP
>
>
>
> <Lex Short> wrote in messagenews:20091014153414rozee415@yahoo.com...
> >I have unsuccessfully tried to create a group subtotal in a report.  I need
> >the report to add together the amounts in only that group.
>
> > I have the following expression in the report:
> > =IIf(Now()>[Begin Date],((Month([End Date])-Month([Begin
> > Date])+1)-(Month(Now())-Month([Begin Date])))*[Monthly Amount],[Inv
> > Amount])
>
> > In the group footer I have a text box that says
> > source:  same formula as listed above
> > Running sum: Over Group
>
> > The result is a running total for the whole report instead of per group..
>
> > If I change it to
> > Running sum: No
>
> > Then the result is the amount of the last record on the page.
>
> > How can I get the report to show the subtotal for that expression per
> > group?
>
> > Many thanks for your help!
>
> > EggHeadCafe - Software Developer Portal of Choice
> > Secure Session State Transfer: ASP to ASP.NET
> >http://www.eggheadcafe.com/tutorials/aspnet/19f8d04a-6bce-4ee0-8d6e-1...- Hide quoted text -
>
> - Show quoted text -