Access 2010 text box running sum stops computations on second to last row

[June7]

You did not respond to my obervation #3 about SumOfTotalUsage.

[sbaptista]

My observations.

3. The total for Total Usage Cost is correct even though the value for record doesn't show. The next column has [txtTotalUsage]/[SumOfTotalUsage]. Where does SumOfTotalUsage come from? I don't see it in the RecordSource. I put a 100,000 in its place and rest of the columns calc but still don't see that row. So bizarre!

SumofTotalUsage is not in the RecordSource. It's a text box located in the report footer that holds the last value of txtTotalUsageRunning, a text box using the running sum technique. When you say "The total for Total Usage Cost is correct even though...", that total is the contents of SumofTotalUsage.

[boblarson]

Unfortunately, in an Access report, the process becomes quite convoluted. I'm resorting to using two text boxes per column, one visible and the other hidden with a running sum because aggregate functions like Sum() are designed to work with the record set, not real time generated data.

That statement makes absolutely NO SENSE whatsoever. A query, using SUM() is perfectly fine to work with "real time generated data." I see absolutely NOTHING which can't be generated inside the query that is the base of the report's recordsource. Would you so kindly try (and this will be a challenge for you) to explain what can't possibly be calculated in the query?

From what you describe, the good news is that you and/or June7 have managed to replicate the behavior I've experienced. The bad news is that you don't see a solution.

The bad news is that I GAVE YOU THE SOLUTION but you refuse to go do it. Your report is extensive enough that I don't want to go do your work for you but will work with you to get it revised. So, don't tell me that I don't see a solution because with 14 years of Access development experience, I think I have at least a little grasp of what is and what is not possible in an Access report.

[boblarson]

Oh, and another thing. You have things in the PAGE HEADER which should NOT be in the PAGE HEADER. It will not be correct. You are attempting things like:

=[BWLowColorVolume]*[BWLowCPC]

which will not display the correct amounts because it will only take the FIRST RECORD and display that value if it is on the page header.

[June7]

I agree with bob that all these calcs should be possible in the query. This leads me to my observation #2 about filtering the data. Your example data includes a date filter but as I described, filtering has no influence on the running sum where you use textbox names instead of field names. I tested this by adding an additional filter criteria for the cost centers and found that the totals were unchanged. You need to do calcs in query and bind textboxes to the calculated fields.

And about the calc with SumOfTotalUsage, it obviously is not working.