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

[sbaptista]

I'm using an Access 2010 report to recreate a spreadsheet that contains rows consisting of fourteen columns of data. Each column is totaled. The data types are numeric and the property sheet format is either Standard or Percent depdening on the column.



In order to track the totals, I utilize two text boxes for each column. One is visible and contains the data for that particular row. The other is hidden and has its Running Sum property set to "Over All". The final totals row consists of text boxes that reference the final result of the running sum text box.

This works up to a point. I see correct totals results for columns 1-10. Then for some reason the running count appears to stop for the second to last row of column 11. (It's always the second to last row). As a result, the totals for this column (11) and subsequent columns (12-14) do not total.

[June7]

I can't replicate your issue.

I presume you are doing this because not every column has data? If no data, the field is Null? Use Report Footer to do an aggregate count of the records. Do the count with an expression that returns 0 or 1 depending on if field has data, then sum that result.

Sum(IIf(IsNull(fieldname),0,1))

[sbaptista]

> I presume you are doing this because not every column has data?

No. It's because the text box for each column contains a calculation. What I need is the sum of all rows for that column. For example, the formula for text box "txtTotalBaseCost" is this:

=[txtPercentofBase]*[BaseEquipmentCharge]

I tried using the Sum function in the footer like so:

Sum(txtTotalBaseCost)

But that doesn't work. So my workaround was to utilize another hidden text box with the same formula that has its Running Sum property set to "Over Group". That has the mixed results that I described.

[June7]

The value in txtPercentofBase is not in the report RecordSource? Where does it come from? I believe aggregate calcs can be done only with fields of the RecordSource.

Because I can't replicate your issue, I can't determine cause. Do you want to provide project for analysis?

[sbaptista]

> The value in txtPercentofBase is not in the report RecordSource? Where does it come from?

It is computed at report runtime using data from the RecordSource.

> I believe aggregate calcs can be done only with fields of the RecordSource.

Yes. This is why I'm attempting to use the running sum technique.

I need to think about providing the project. 1) It's a large file (209M) although I could strip it first and 2) it has customer data. I don't think they would be willing to let me send it off. I'll get back to you on that.

[sbaptista]

How to I upload the database?

[June7]

Attach to post. Click Go Advanced below post editor. Manage Attachments is below the Advanced editor.

Make copy or extract relevant objects to another project, remove confidential data, run Compact & Repair, zip if still large.

[sbaptista]

See attachment. To view report:

1. Click report "Cost Center Billing by Month"
2. At prompt, type 7/1/2011
3. Scroll to bottom of column "Allocation % of base"
4. See second to last row where running sum stops.

[boblarson]

I don't have access to 2010 from work but did you put the text box for the running sum in the group FOOTER and NOT the group header? It should be in the group footer.

[sbaptista]

Thanks for your help. I tried publishing in a earlier Access format (2003) but no go because I'm using features incompatible w/ earler versions.

I'm putting the totals in the _report_ footer. There is no group footer in this report.

[boblarson]

I just looked at your file and I believe I may know what the problem is. I believe it is because you are doing calculations using the CONTROL names and not the FIELD names. In Reports you need to use the FIELD names in calculations wherever possible.

[June7]

My observations.

1. Doesn't matter how many records are listed. Even when I filtered to only two records, still blanks those textboxes.

2. Filtering to exclude some cost centers doesn't change the Total. Why is that?

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!

I've never used running sum so not familiar with its quirks.

BTW, before uploading file should run Compact & Repair, cut file size from 12mb to 7mb.

[boblarson]

Very bizarre. Two observations, but unfortunately no solution.

1. Doesn't matter how many records are listed. Even when I filtered to only two records, still blanks those textboxes.

Did you change to using the FIELD names instead of using the text box names. And any that are trying to use other text boxes which are doing calculations would need to be replaced with using the full calculation using the FIELD names.

2. Filtering to exclude some cost centers doesn't change the Total. Why is that?

If using with the FIELDS it should.

[June7]

Bob, edited my previous post, more comments.

[sbaptista]

I just looked at your file and I believe I may know what the problem is. I believe it is because you are doing calculations using the CONTROL names and not the FIELD names. In Reports you need to use the FIELD names in calculations wherever possible.

Sanity check:
FIELD names = database fields
CONTROL names = values of report text boxes.

I would use FIELD names exclusively if I could but I don't see how that's possible because the formulas used in calculations occur at report run time not in the database or at query time. The text boxes are used as calculation variables.

That may not be a good idea but since Access does not flag it as an error when I use the CONTROL name of the text box in a calculation, I assume it's allowed and that it works. That's apparently a bad assumption.

Note that this report started its life as a table in an Excel spreadsheet. In Excel, these calculations are trivial--columns of data are simply summed. This particular spreadsheet requires sub calculations but Excel handles those routinely.

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.

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.

Suggestions?