Can't get running sum to work

[RichO]

Well, I swear I have done this before with a report but I can't seem to figure it out now.

I have a report with a field (txtAmount) containing a condition such as:

=IIf([MyField]="Bob", 100, 50)

In the report footer I want to sum this field but =Sum([txtAmount]) doesn't work.

I tried using VBA in the format property of the detail section

total = total + txtAmount

or

total = total + IIf([MyField]="Bob", 100, 50)

but both of these result in an error "You entered an expression that has no value".

It sounds so simple but I can't figure it out. Any help would be appreciated. Thanks.

 

[Beetle]

Option 1;

Use your expression in a calculated field in a query, use the query as the Record Source of the report and Sum the calculated field.

Option 2;

Re-use the expression in the calculated control in your report footer;

=Sum(IIf([MyField]="Bob", 100, 50))

 

[RichO]

=Sum(IIf([MyField]="Bob", 100, 50))

Well this was the very first thing I tried but I was told that it wasn't calculating the total correct so I was under the impression that it couldn't calculate right because of each line being a separate record with individual conditions.

I will have to toy around with that method more and see what the problem is and see if a calculated field in the query gives the same result.

Thanks.

 

[philb316]

I don't know if this response is directly related to the problem but I always have to remind myself that calculated fields in a report cannot be summed because their values are only present for the instance of the single row of data.
So yes, either I
1) put the calculation in the underlying query
OR
2) for running sums / totals I create hidden unbound textboxes = calculated field, in the details section, which do running sums, then in the totals section of the report simply have yet another unbound textbox set to equal the hidden running sum textbox. Messy, but it works well everytime. I have to do this method whenever I have a complex VBA fn attached to the calculated field.

Probably as clear as mud but if you a want an example I can post one.

 

[RichO]

Phil,

I don't have a problem with #2 but an example might be helpful because I'm a little confused.

The visible textbox on the report is a calculated field in itself so how do you run a sum within a text box in the detail section?


Thanks

 

[philb316]

In the details section:
1) txt1MyCalculatedTextbox
- do your calculation in this txtbox

2) txt2MyRunningSumtextBox
- set the control source to =[txt1Mycalculatedtextbox]
- set the Running Sum property to Over Group or Over All as usual

In the Footer section
3) txt3MyTotalTextBox
- set the control source to =[txt2MyRunningSumTextBox]

Like I said, this is brute force stuff but sometimes ya gotta go there
Also, I made the mistake of making a calculated textbox invisible and disabled a couple months back. The disabled setting means that the calculation does not occur, Which in turn means that any dependent calcs are not correct. Cost me 8+ hours to debug. :-(

Hope this helps!

 

[RichO]

Perfect and easy. I had completely forgot about the running sum property since I haven't used it in ages.

Thanks!

 

[philb316]

Glad it worked for you.