Strange running sum problem

[Van2]

Hi, I have the strangest problem with running sums in a report.

The running sum is that of a number field of expenditures and is located in
the Group footer of the report. The report comes from a simple query and I
only have one grouping and one sort. The sort is by month and I want to get a
running total for each month.

Most of the values in the expenditure field are null, there is only one
value of 500 in the first month. The sum works for the first month, but
instead of showing 500 for the second and after, it behaves extremely
strangely, sometimes it shows 0 and sometimes 500. As I scroll back and forth
it keeps changing as it recalculates.

Is this a known bug, or are the nulls confusing it for some reason?

Thanks

 

[Allen Browne]

It sounds like Access is not recognising the field as numeric. If it
believes it is Text, it will have trouble summing it.

Tell us about this field. Is it a calculated expression in your query? If
so, run the query directly and see if the column displays left-aligned (like
text) or right-aligned (like a number.) If left, you need to alter the
expression so that JET knows it's a number. For example, you may be able to
use IIf() instead of Nz() as shown here:
http://allenbrowne.com/QueryPerfIssue.html#IsNull
Or you may need to typecast:
http://allenbrowne.com/ser-45.html

After that, set the Format property of the Running Sum text box on the
report to something numeric (e.g. Currency or General Number.)

 

[Allen Browne]

It sounds like Access is not recognising the field as numeric. If it
believes it is Text, it will have trouble summing it.

Tell us about this field. Is it a calculated expression in your query? If
so, run the query directly and see if the column displays left-aligned (like
text) or right-aligned (like a number.) If left, you need to alter the
expression so that JET knows it's a number. For example, you may be able to
use IIf() instead of Nz() as shown here:
http://allenbrowne.com/QueryPerfIssue.html#IsNull
Or you may need to typecast:
http://allenbrowne.com/ser-45.html

After that, set the Format property of the Running Sum text box on the
report to something numeric (e.g. Currency or General Number.)

 

[Van2]

Thanks for the help, the fields were being interpreted correctly as numbers,
but still this problem exist, so apparently I discovered a bug in the report
view module when calculating running sums. When I do print preview it
calculates correctly every time, so apparently the bug is ONLY in the report
view of the report.

 

[Allen Browne]

Is there any code in the Format or Print event of the sections of the
report? Do your totals rely on this code?

The code won't fire in Report view (or in Layout view.)

 

[Van2]

No, there isn't any code in the entire project,
it's just simply "=Sum([expenditures])"
set as a running sum.

As I scroll back and forth in report view sometimes it gets it correctly
(like 2 out of five times) and sometimes it gives totally strange results,
Every time the running sum updates I can't tell whether it will be correct
or not,
so this is an unusual bug, I'm suprised it is not yet known.

It's interesting that the same thing calculates correctly every single time
when printing or doing print preview.

 

[Allen Browne]

In case it is a bug, make sure you have applied Service Pack 2 for Office
2007:
http://www.microsoft.com/downloads/...18-79ea-46c6-8a81-9db49b4ab6e5&displaylang=en

If the problem persists after that, let's get some very specific info.
Please correct whichever assumptions below are wrong:

a) The report is bound to a query.
This query has a field named Expenditures.
When you view the query output, you see the Expenditures field right-aligned
(as a number), not left-aligned (as text.)

b) The report has a text box with these properties:
Control Source =Sum([expenditures])
Running Sum Over All (or Over Group?)

c) The text box is in what section of the report?
Detail? Group Footer? Report Footer? Page Footer?

I'm trying to follow exactly where Report View could be getting it wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

No, there isn't any code in the entire project,
it's just simply "=Sum([expenditures])"
set as a running sum.

As I scroll back and forth in report view sometimes it gets it correctly
(like 2 out of five times) and sometimes it gives totally strange results,
Every time the running sum updates I can't tell whether it will be correct
or not,
so this is an unusual bug, I'm suprised it is not yet known.

It's interesting that the same thing calculates correctly every single
time
when printing or doing print preview.

Is there any code in the Format or Print event of the sections of the
report? Do your totals rely on this code?

The code won't fire in Report view (or in Layout view.)