running Sums

[Todd]

Hi,
I'm trying to do the following in a group footer:
=SUM(IIf(DateDiff("d",[FinalPaymentDate],Now()+60)
Between 1 And 60,[AR],0))

when I do this I get an error that says "Aggregate
functions are only allowed in output fields of the record
source"

So I tried setting the control source to:
=IIf(DateDiff("d",[FinalPaymentDate],Now()+60) Between 1
And 60,[AR],0)
and then set the running sum to "over group". But, when
I set it up that way, it gives me a cumulative sum over
all the groups, rather than individual group sums.
Anybody have any suggestions? (I realize I could include
the iif clause in a query, and then do a =sum
([MyQueryiifField]), but it seems like I should be able
to do this in the report)

Thanks for you Help

 

[Marshall Barton]

I'm trying to do the following in a group footer:
=SUM(IIf(DateDiff("d",[FinalPaymentDate],Now()+60)
Between 1 And 60,[AR],0))

when I do this I get an error that says "Aggregate
functions are only allowed in output fields of the record
source"

That looks ok to me. Are you sure that FinalPaymentDate and
AR are both fields in the report's record source table
query?

So I tried setting the control source to:
=IIf(DateDiff("d",[FinalPaymentDate],Now()+60) Between 1
And 60,[AR],0)
and then set the running sum to "over group". But, when
I set it up that way, it gives me a cumulative sum over
all the groups, rather than individual group sums.
Anybody have any suggestions? (I realize I could include
the iif clause in a query, and then do a =sum
([MyQueryiifField]), but it seems like I should be able
to do this in the report)

If you want a running sum over the group, then the text box
with the expression must be in the detail section. The
group footer text box would then display the group total by
refering to the detail text box.