Report Sums

  • Thread starter Thread starter BryanK
  • Start date Start date
B

BryanK

I'm trying to programmatically calculate report sums in my
report. My function has

Function CalcTotal(R As Report)

GrandTotal = GrandTotal + Nz(R![<mytextbox>], 0)
CalcTotal = R![<mytextbox>]

End Function

The problem I have is when I go ahead print my report...
all my total are doubled. The same goes true when I
change my margins. What's happening is access is
calculating the totals again. How do I keep the sums as
they are once they appear on the preview and keep access
from recalculating the grand total sums?
 
BryanK said:
I'm trying to programmatically calculate report sums in my
report. My function has

Function CalcTotal(R As Report)

GrandTotal = GrandTotal + Nz(R![<mytextbox>], 0)
CalcTotal = R![<mytextbox>]

End Function

The problem I have is when I go ahead print my report...
all my total are doubled. The same goes true when I
change my margins. What's happening is access is
calculating the totals again. How do I keep the sums as
they are once they appear on the preview and keep access
from recalculating the grand total sums?
Click to expand...

Even if you do reset the total variable in the report
header's format or print event, you would still have a good
chance of getting the wrong result. Because there are so
many complex things going on in a report (KeepTogether,
CanGrow/CanShrink, etc), the records in a report are not
processed in a sequential manner. This means that you can
not calculate values across multiple records using code in
an event procedure.

To calculate aggregate values, you must either use a
built-in aggregate function (Count, Sum, Avg, etc), use a
text box with its RunningSum property set to Over Group or
Over All as appropriate, or precalculate the total in the
report's record source query..
 
Marsh,
The problem I have is that the report is an aging
report... that means I have a Current Column, 30 day
column, 60 day column, 90 day column and a 120 day
column. In my detail section there are 5 unbound text
fields and 1 hidden text field linked to the field in a
table. Each time the detail goes through it looks for
todays date and gives the hidden text field value to one
of the appropriate unbound text fields. The other 4
receive a null value.

How in this scenario can I sum the totals of the report?
It works when I open the report... but as soon as I print
or change the margins for the page... the totals change.

I have to think of another way to do this. I was thinking
of using a recordsource but how do I keep track of the
columns?
-----Original Message-----
BryanK said:
I'm trying to programmatically calculate report sums in my
report. My function has

Function CalcTotal(R As Report)

GrandTotal = GrandTotal + Nz(R![<mytextbox>], 0)
CalcTotal = R![<mytextbox>]

End Function

The problem I have is when I go ahead print my report...
all my total are doubled. The same goes true when I
change my margins. What's happening is access is
calculating the totals again. How do I keep the sums as
they are once they appear on the preview and keep access
from recalculating the grand total sums?
Click to expand...

Even if you do reset the total variable in the report
header's format or print event, you would still have a good
chance of getting the wrong result. Because there are so
many complex things going on in a report (KeepTogether,
CanGrow/CanShrink, etc), the records in a report are not
processed in a sequential manner. This means that you can
not calculate values across multiple records using code in
an event procedure.

To calculate aggregate values, you must either use a
built-in aggregate function (Count, Sum, Avg, etc), use a
text box with its RunningSum property set to Over Group or
Over All as appropriate, or precalculate the total in the
report's record source query..
Click to expand...
 
BryanK said:
The problem I have is that the report is an aging
report... that means I have a Current Column, 30 day
column, 60 day column, 90 day column and a 120 day
column. In my detail section there are 5 unbound text
fields and 1 hidden text field linked to the field in a
table. Each time the detail goes through it looks for
todays date and gives the hidden text field value to one
of the appropriate unbound text fields. The other 4
receive a null value.

How in this scenario can I sum the totals of the report?
It works when I open the report... but as soon as I print
or change the margins for the page... the totals change.
Click to expand...

Depending on what else the report is doing (grouping, etc),
you could probably use RunningSum on the unbound text boxes.

I have to think of another way to do this. I was thinking
of using a recordsource but how do I keep track of the
columns?
Click to expand...

A good way to approach this is to make the report's record
source query assign the amount values to calculated fields.
Might be something like:

SELECT datefield,
amount,
DateDiff("d", datefield, Date()) As Days,
IIf(Days < 30, amount, Null) As Age0,
IIf(Days >= 30 And Days < 60, amount, Null) As Age30,
IIf(Days >= 60 And Days < 90, amount, Null) As Age60,
IIf(Days >= 90 And Days < 120, amount, Null) As Age90,
IIf(Days >= 120, amount, Null) As Age120,
FROM table
WHERE whatever

Now the report wouldn't have to worry about any of this
stuff and the totals would just be report footer text boxes
with expressions like:
=Sum([Age60])
 
Back
Top