Sum of another sum

[James O]

Hey all I have a question, hopefully I will be able to write this in a clear
way.

I have a report that is making me crazy. In one header I have it grouped by
vendor name in the next header its grouped by date. In the date footer I have
a sum in a text box (text20) that looks like this:

=IIf(Sum([Dollaramt])>1000,1000,Sum([dollaramt]))

So that way the daily sum amount cannot go over 1000 per day even though the
amount of occurences may go well over 1000. I want to show a monthly total
but if I put a regular sum in the Vendor footer it adds all the errors for
the day which is well over 1000 most times and the sum goes higher then it
should. If I use the same forumula as above then i only get 1000 per month
which is too low. And if I try to put a text box in the Vendor footer that
looks like:

=Sum([text20])

then when I run the report I get a dialog box requesting the value of
text20. Any help is very much appreciated. Thanks!

James O

 

[Marshall Barton]

I have a report that is making me crazy. In one header I have it grouped by
vendor name in the next header its grouped by date. In the date footer I have
a sum in a text box (text20) that looks like this:

=IIf(Sum([Dollaramt])>1000,1000,Sum([dollaramt]))

So that way the daily sum amount cannot go over 1000 per day even though the
amount of occurences may go well over 1000. I want to show a monthly total
but if I put a regular sum in the Vendor footer it adds all the errors for
the day which is well over 1000 most times and the sum goes higher then it
should. . . .

Add a text box named txtRunDailySum to the date footer
section using the expression =text20 and set this text
box's RunningSum property to Over Group. Then the vendor
footer text box can display the total using the expression
=txtRunDailySum

 

[James O]

That did the trick. Thanks so much!!

I have a report that is making me crazy. In one header I have it grouped by
vendor name in the next header its grouped by date. In the date footer I have
a sum in a text box (text20) that looks like this:

=IIf(Sum([Dollaramt])>1000,1000,Sum([dollaramt]))

So that way the daily sum amount cannot go over 1000 per day even though the
amount of occurences may go well over 1000. I want to show a monthly total
but if I put a regular sum in the Vendor footer it adds all the errors for
the day which is well over 1000 most times and the sum goes higher then it
should. . . .

Add a text box named txtRunDailySum to the date footer
section using the expression =text20 and set this text
box's RunningSum property to Over Group. Then the vendor
footer text box can display the total using the expression
=txtRunDailySum