Report: Running Total Problem

[Bill Dika]

Hi

I am trying to calculate a running total of a calculated textbox
(tbAtStandard) in GroupFooter1 for placement in a textbox
(tbTotalAtStandard) on my report in Groupfooter0.

The problem that I am having is that sometimes the correct total shows
up in print preview and sometimes it doesn't. Sometimes it is higher
and sometimes it is lower (than the correct amount) and I cannot make
any sense of the difference. The difference seemingly does not tie
into anything on the report (e.g. double counting an item or leaving
off an item). Another anomaly is that the print preview and the actual
printing sometimes produce different results, again without any
pattern (but sometimes correct).

The code I am using seems to me to be quite simple and is as follows:

Option Compare Database
Dim vbatbAtStandard As Double
Dim vbatbAtCost As Double
Private Sub GroupFooter0_Print(Cancel As Integer, PrintCount As
Integer)
'------ post the running sum to the report and zero the variable
for next group -------
Me!tbTotalAtStandard = vbatbAtStandard
vbatbAtStandard = 0
'---------------------------------------------------------------------------
Me!tbTotalAtCost = vbatbAtCost
vbatbAtCost = 0
End Sub

Private Sub GroupFooter1_Print(Cancel As Integer, PrintCount As
Integer)
If PrintCount = 1 Then
'------- calculate the running sum --------
vbatbAtStandard = vbatbAtStandard + Me!tbAtStandard
'------------------------------------------
vbatbAtCost = vbatbAtCost + Me!tbAtCost
End If
End Sub

Any help would be much appreciated.

Regards,
Bill Dika

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ugh . . . I've never had any success using VBA to calculate totals in
reports. I've found that using a TextBox's RunningSum property is
simpler & more accurate.

== Simple totals

Since you want to get the Sum of a calculated control you can do
something like this:

Detail Section

ControlSource: Data1 Data2 =Data1+Data2
Name: Data1 Data2 txtDetailTotal

Group Footer Section

ControlSource: =Sum(Data1) =Sum(Data2) =txtSumData1+txtSumData2
Name: txtSumData1 txtSumData2 txtSumAll
== Running Sum totals

Say I wanted to keep a running sum of the =Data1+Data2 calculation
(txtDetailTotal control). I'd set up another TextBox control's
properties like this in the Detail section:

Name: txtDetailRunSum
ControlSource: =txtDetailTotal
Visible: False
RunningSum: Over All

Then in the group footer section I'd have a TextBox set up like this:

ControlSource: =txtDetailRunSum
Visible: True

This control would show the running sum for all groupings.

Read the Access help articles on "RunningSum Property" for more info.

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP7WzEoechKqOuFEgEQIvcwCgvqkhn4luFwWdaegd/UF50DtKg2UAoNX4
DZeon75WOL5v7Mk4CpK6ITPf
=ZoA1
-----END PGP SIGNATURE-----
Bill Dika wrote:

Hi

I am trying to calculate a running total of a calculated textbox
(tbAtStandard) in GroupFooter1 for placement in a textbox
(tbTotalAtStandard) on my report in Groupfooter0.

The problem that I am having is that sometimes the correct total shows
up in print preview and sometimes it doesn't. Sometimes it is higher
and sometimes it is lower (than the correct amount) and I cannot make
any sense of the difference. The difference seemingly does not tie
into anything on the report (e.g. double counting an item or leaving
off an item). Another anomaly is that the print preview and the actual
printing sometimes produce different results, again without any
pattern (but sometimes correct).

The code I am using seems to me to be quite simple and is as follows:

Option Compare Database
Dim vbatbAtStandard As Double
Dim vbatbAtCost As Double
Private Sub GroupFooter0_Print(Cancel As Integer, PrintCount As
Integer)
'------ post the running sum to the report and zero the variable
for next group -------
Me!tbTotalAtStandard = vbatbAtStandard
vbatbAtStandard = 0
'---------------------------------------------------------------------------
Me!tbTotalAtCost = vbatbAtCost
vbatbAtCost = 0
End Sub

Private Sub GroupFooter1_Print(Cancel As Integer, PrintCount As
Integer)
If PrintCount = 1 Then
'------- calculate the running sum --------
vbatbAtStandard = vbatbAtStandard + Me!tbAtStandard
'------------------------------------------
vbatbAtCost = vbatbAtCost + Me!tbAtCost
End If
End Sub

Any help would be much appreciated.

Regards,
Bill Dika

[Tom van Stiphout]

On 14 Nov 2003 12:28:02 -0800, bd*****@yahoo.com (Bill Dika) wrote:

It seems you're writing code to calculate the sum. This code may be
called multiple times by Access. That's what the PrintCount argument
is for. Set a breakpoint and you'll see what I mean. Even "if
PrintCount = 1" to me seems a bit too scary. For example your function
may be called again with PrintCount=1 if you first preview the report
and then print it from there.

The standard way of calculating a running sum is to specify this at
design time in a control. See the RunningSum property of a textbox.
Try that first.

-Tom.

Hi

I am trying to calculate a running total of a calculated textbox
(tbAtStandard) in GroupFooter1 for placement in a textbox
(tbTotalAtStandard) on my report in Groupfooter0.

The problem that I am having is that sometimes the correct total shows
up in print preview and sometimes it doesn't. Sometimes it is higher
and sometimes it is lower (than the correct amount) and I cannot make
any sense of the difference. The difference seemingly does not tie
into anything on the report (e.g. double counting an item or leaving
off an item). Another anomaly is that the print preview and the actual
printing sometimes produce different results, again without any
pattern (but sometimes correct).

The code I am using seems to me to be quite simple and is as follows:

Option Compare Database
Dim vbatbAtStandard As Double
Dim vbatbAtCost As Double
Private Sub GroupFooter0_Print(Cancel As Integer, PrintCount As
Integer)
'------ post the running sum to the report and zero the variable
for next group -------
Me!tbTotalAtStandard = vbatbAtStandard
vbatbAtStandard = 0
'---------------------------------------------------------------------------
Me!tbTotalAtCost = vbatbAtCost
vbatbAtCost = 0
End Sub

Private Sub GroupFooter1_Print(Cancel As Integer, PrintCount As
Integer)
If PrintCount = 1 Then
'------- calculate the running sum --------
vbatbAtStandard = vbatbAtStandard + Me!tbAtStandard
'------------------------------------------
vbatbAtCost = vbatbAtCost + Me!tbAtCost
End If
End Sub

Any help would be much appreciated.

Regards,
Bill Dika

[Bill Dika]

Thanks guys for responding to my post.

MGFoster & Tom

My application is a time keeping system.

My GroupFooter1 is Employee. In the Detail Section I show start and
end times for given dates. In GroupFooter1 I total up the hours using
a Sum(EndTime-StarTime) and then I have a calculated control in
GroupFooter1 that uses a DLookup to get the charge rate for that
employee and multiply it times the total hours for that employee.

Something like:
GroupFooter1
Control Name = tbAtStandard
Control Source = DLookup(EmployeeRate) X Sum(EndTime-StartTime)
This seems to work fine.

The problem comes in accumulating tbAtStandard for all employees in
GroupFooter0 which is Client. I'm not sure if and how I can use the
RunningSum property to get the result. If I set up in GroupFooter0 a
textbox with control source = sum(tbAtStandard), when I run the report
it asks me to input tbAtStandard.

That is why I have tried to use code to keep a running total of
tbAtStandard. Since my first post I have tried putting my code into
the Format events instead of the Print events but I still don't get
the correct results.

MGFoster
I don't think I can use your suggestion which relies on calculations
in the detail section because the EmployeeRate changes with each new
GroupFooter1.

Am I missing something obvious?

Many thanks.

Regards,
Bill Dika

Tom van Stiphout <to*****@no.spam.cox.net> wrote in message news:<br********************************@4ax.com>. ..

On 14 Nov 2003 12:28:02 -0800, bd*****@yahoo.com (Bill Dika) wrote:

It seems you're writing code to calculate the sum. This code may be
called multiple times by Access. That's what the PrintCount argument
is for. Set a breakpoint and you'll see what I mean. Even "if
PrintCount = 1" to me seems a bit too scary. For example your function
may be called again with PrintCount=1 if you first preview the report
and then print it from there.

The standard way of calculating a running sum is to specify this at
design time in a control. See the RunningSum property of a textbox.
Try that first.

-Tom.

Hi

I am trying to calculate a running total of a calculated textbox
(tbAtStandard) in GroupFooter1 for placement in a textbox
(tbTotalAtStandard) on my report in Groupfooter0.

The problem that I am having is that sometimes the correct total shows
up in print preview and sometimes it doesn't. Sometimes it is higher
and sometimes it is lower (than the correct amount) and I cannot make
any sense of the difference. The difference seemingly does not tie
into anything on the report (e.g. double counting an item or leaving
off an item). Another anomaly is that the print preview and the actual
printing sometimes produce different results, again without any
pattern (but sometimes correct).

The code I am using seems to me to be quite simple and is as follows:

Option Compare Database
Dim vbatbAtStandard As Double
Dim vbatbAtCost As Double
Private Sub GroupFooter0_Print(Cancel As Integer, PrintCount As
Integer)
'------ post the running sum to the report and zero the variable
for next group -------
Me!tbTotalAtStandard = vbatbAtStandard
vbatbAtStandard = 0
'---------------------------------------------------------------------------
Me!tbTotalAtCost = vbatbAtCost
vbatbAtCost = 0
End Sub

Private Sub GroupFooter1_Print(Cancel As Integer, PrintCount As
Integer)
If PrintCount = 1 Then
'------- calculate the running sum --------
vbatbAtStandard = vbatbAtStandard + Me!tbAtStandard
'------------------------------------------
vbatbAtCost = vbatbAtCost + Me!tbAtCost
End If
End Sub

Any help would be much appreciated.

Regards,
Bill Dika

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

In GroupFooter1 section put a TextBox control next the control
"tbAtStandard." Set its Properties like so:

Name: txtSumStandard
Control Source: =[tbAtStandard]
Running Sum: Over Group
Visible: No

The "Over Group" running sum means each time the footer/header section
changes to a new grouping item the running sum is reset to zero.
"Over All" means the running sum is kept for the total report. See
the "RunningSum Property" Help article for more info.

In the GroupFooter0 section put a TextBox in the same column space as
the control "tbAtStandard." Set its Properties like so:

Name: txtFooter_SumStandard
Control Source: =[txtSumStandard]
Running Sum: No
Visible: Yes

The Running Sum property can be a PITA until understood. Even now, I
have to fiddle around w/ Running Sum reports until I get them right.

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP7sU+4echKqOuFEgEQJ9OgCcCYdRdKcBbF+pMDBvl/noe5TzIzsAmwX8
gjX2aZq/rrQv+VzBTp1jo7oR
=ZL2Y
-----END PGP SIGNATURE-----

Bill Dika wrote:

Thanks guys for responding to my post.

MGFoster & Tom

My application is a time keeping system.

My GroupFooter1 is Employee. In the Detail Section I show start and
end times for given dates. In GroupFooter1 I total up the hours using
a Sum(EndTime-StarTime) and then I have a calculated control in
GroupFooter1 that uses a DLookup to get the charge rate for that
employee and multiply it times the total hours for that employee.

Something like:
GroupFooter1
Control Name = tbAtStandard
Control Source = DLookup(EmployeeRate) X Sum(EndTime-StartTime)
This seems to work fine.

The problem comes in accumulating tbAtStandard for all employees in
GroupFooter0 which is Client. I'm not sure if and how I can use the
RunningSum property to get the result. If I set up in GroupFooter0 a
textbox with control source = sum(tbAtStandard), when I run the report
it asks me to input tbAtStandard.

That is why I have tried to use code to keep a running total of
tbAtStandard. Since my first post I have tried putting my code into
the Format events instead of the Print events but I still don't get
the correct results.

MGFoster
I don't think I can use your suggestion which relies on calculations
in the detail section because the EmployeeRate changes with each new
GroupFooter1.

Am I missing something obvious?

Many thanks.

Regards,
Bill Dika

Tom van Stiphout <to*****@no.spam.cox.net> wrote in message news:<br********************************@4ax.com>. ..

On 14 Nov 2003 12:28:02 -0800, bd*****@yahoo.com (Bill Dika) wrote:

It seems you're writing code to calculate the sum. This code may be
called multiple times by Access. That's what the PrintCount argument
is for. Set a breakpoint and you'll see what I mean. Even "if
PrintCount = 1" to me seems a bit too scary. For example your function
may be called again with PrintCount=1 if you first preview the report
and then print it from there.

The standard way of calculating a running sum is to specify this at
design time in a control. See the RunningSum property of a textbox.
Try that first.

-Tom.

Hi

I am trying to calculate a running total of a calculated textbox
(tbAtStandard) in GroupFooter1 for placement in a textbox
(tbTotalAtStandard) on my report in Groupfooter0.

< snip >