messed up totals in report

D

Dennis

Is there anyway to prevent my totals from messing-up
whenever the totals occur after a page break?

Here is the code that I'm using:

Option Compare Database
'
**********************************************************
' Creating Global variables
'
**********************************************************
Public SourceTotalAwarded As Currency
Public SourceTotalRequested As Currency
Public YearTotalRequested As Currency
Public YearTotalAwarded As Currency
Public GrandTotalRequested As Currency
Public GrandTotalAwarded As Currency

**********************************************************
' Year Footer
'
' Action:
' Accumulating Grand Totals from Yearly Totals
' Assigning Year Totals tp Year Totals fields
' Reseting Yearly Totals to zero
'
**********************************************************
Private Sub GroupFooter1_Format(Cancel As Integer,
FormatCount As Integer)
GrandTotalRequested = GrandTotalRequested +
YearTotalRequested
GrandTotalAwarded = GrandTotalAwarded +
YearTotalAwarded
[Year_Total_Requested] = YearTotalRequested
[Year_Total_Awarded] = YearTotalAwarded
End Sub


**********************************************************
' Year Header
'
' Action:
' Reset Year Totals to zero
'
**********************************************************

Private Sub GroupHeader0_Format(Cancel As Integer,
FormatCount As Integer)
YearTotalAwarded = 0
YearTotalRequested = 0
End Sub

'
**********************************************************
' BUNumber Header
'
' Action:
' Calculate Source Totals
'
**********************************************************
Private Sub GroupHeader1_Format(Cancel As Integer,
FormatCount As Integer)
If [Total_Amount_Awarded] > 0 Then
SourceTotalAwarded = SourceTotalAwarded +
[Total_Amount_Awarded]
End If
If [Total_Amount_Requested] > 0 Then
SourceTotalRequested = SourceTotalRequested +
[Total_Amount_Requested]
End If
End Sub

' ********************************************************
' Open Report
'
' Action:
' Set all variables to zero
'
**********************************************************
Private Sub Report_Open(Cancel As Integer)
SourceTotalAwarded = 0
SourceTotalRequested = 0
YearTotalRequested = 0
YearTotalAwarded = 0
GrandTotalRequested = 0
GrandTotalAwarded = 0
End Sub

'
**********************************************************
' Report Footer
'
' Action:
' Assigning Grand Totals to Gand Totals fields
'
'*********************************************************
Private Sub ReportFooter_Format(Cancel As Integer,
FormatCount As Integer)
[Grand_Total_Requested] = GrandTotalRequested
[Grand_Total_Awarded] = GrandTotalAwarded
End Sub

Private Sub ReportHeader_Format(Cancel As Integer,
FormatCount As Integer)
SourceTotalAwarded = 0
SourceTotalRequested = 0
YearTotalRequested = 0
YearTotalAwarded = 0
GrandTotalRequested = 0
GrandTotalAwarded = 0

End Sub


'
**********************************************************
' Source Footer
'
' Action:
' Assign Source Totals to Source Total fields
' Accumulats Yearly Totals from Source Totals
' Reset Source Totals to zero
'
*********************************************************

Private Sub SourceFooter_Print(Cancel As Integer,
PrintCount As Integer)
[Source_Total_Amount_Awarded] = SourceTotalAwarded
YearTotalAwarded = YearTotalAwarded +
[Source_Total_Amount_Awarded]
[Source_Total_Amount_Requested] = SourceTotalRequested
YearTotalRequested = YearTotalRequested +
[Source_Total_Amount_Requested]
SourceTotalAwarded = 0
SourceTotalRequested = 0
End Sub
 
J

Jeff Boyce

Dennis

Your definition of "messed up" may not match mine. Are you asking the
newsgroup readers to review all your code to identify where it might be
breaking, in some un-defined way?

More info, please...!

Good luck

Jeff Boyce
<Access MVP>
 
D

Duane Hookom

I agree with Jeff.
I have created hundreds of reports and have never resorted to accumulating
totals in report code. Is there a reason why you are using code versus
setting a control source to something like:
=Sum([ANumericField])
Also, I find your code hard to read since it isn't clear what is a variable
and what might be a control on the report. I recommend naming your text
boxes like "txtSourceTotal" or whatever. Then, in your code, use
If Me.txtSourceTotal ...
SomeVal = SomeVal + Me.txtSourceTotal
--
Duane Hookom
Microsoft Access MVP


Dennis said:
Is there anyway to prevent my totals from messing-up
whenever the totals occur after a page break?

Here is the code that I'm using:

Option Compare Database
'
**********************************************************
' Creating Global variables
'
**********************************************************
Public SourceTotalAwarded As Currency
Public SourceTotalRequested As Currency
Public YearTotalRequested As Currency
Public YearTotalAwarded As Currency
Public GrandTotalRequested As Currency
Public GrandTotalAwarded As Currency

**********************************************************
' Year Footer
'
' Action:
' Accumulating Grand Totals from Yearly Totals
' Assigning Year Totals tp Year Totals fields
' Reseting Yearly Totals to zero
'
**********************************************************
Private Sub GroupFooter1_Format(Cancel As Integer,
FormatCount As Integer)
GrandTotalRequested = GrandTotalRequested +
YearTotalRequested
GrandTotalAwarded = GrandTotalAwarded +
YearTotalAwarded
[Year_Total_Requested] = YearTotalRequested
[Year_Total_Awarded] = YearTotalAwarded
End Sub


**********************************************************
' Year Header
'
' Action:
' Reset Year Totals to zero
'
**********************************************************

Private Sub GroupHeader0_Format(Cancel As Integer,
FormatCount As Integer)
YearTotalAwarded = 0
YearTotalRequested = 0
End Sub

'
**********************************************************
' BUNumber Header
'
' Action:
' Calculate Source Totals
'
**********************************************************
Private Sub GroupHeader1_Format(Cancel As Integer,
FormatCount As Integer)
If [Total_Amount_Awarded] > 0 Then
SourceTotalAwarded = SourceTotalAwarded +
[Total_Amount_Awarded]
End If
If [Total_Amount_Requested] > 0 Then
SourceTotalRequested = SourceTotalRequested +
[Total_Amount_Requested]
End If
End Sub

' ********************************************************
' Open Report
'
' Action:
' Set all variables to zero
'
**********************************************************
Private Sub Report_Open(Cancel As Integer)
SourceTotalAwarded = 0
SourceTotalRequested = 0
YearTotalRequested = 0
YearTotalAwarded = 0
GrandTotalRequested = 0
GrandTotalAwarded = 0
End Sub

'
**********************************************************
' Report Footer
'
' Action:
' Assigning Grand Totals to Gand Totals fields
'
'*********************************************************
Private Sub ReportFooter_Format(Cancel As Integer,
FormatCount As Integer)
[Grand_Total_Requested] = GrandTotalRequested
[Grand_Total_Awarded] = GrandTotalAwarded
End Sub

Private Sub ReportHeader_Format(Cancel As Integer,
FormatCount As Integer)
SourceTotalAwarded = 0
SourceTotalRequested = 0
YearTotalRequested = 0
YearTotalAwarded = 0
GrandTotalRequested = 0
GrandTotalAwarded = 0

End Sub


'
**********************************************************
' Source Footer
'
' Action:
' Assign Source Totals to Source Total fields
' Accumulats Yearly Totals from Source Totals
' Reset Source Totals to zero
'
*********************************************************

Private Sub SourceFooter_Print(Cancel As Integer,
PrintCount As Integer)
[Source_Total_Amount_Awarded] = SourceTotalAwarded
YearTotalAwarded = YearTotalAwarded +
[Source_Total_Amount_Awarded]
[Source_Total_Amount_Requested] = SourceTotalRequested
YearTotalRequested = YearTotalRequested +
[Source_Total_Amount_Requested]
SourceTotalAwarded = 0
SourceTotalRequested = 0
End Sub
Click to expand...
 
M

Marshall Barton

Dennis said:
Is there anyway to prevent my totals from messing-up
whenever the totals occur after a page break?
Click to expand...
[snip a lot of useless code]

I will go further than Jeff and Duane, there is nothing
wrong with your code, except that this approach can not work
reliably. For many reasons, reports do not process their
data sequentially, so event procedures can not accumulate
any kind of a total from one record to another.

As Duane said either use a text box with an aggregate
function, a running sum text box, or a subquery in the
report's record source query.
 
G

Guest

I'm trying to calculate running totlas, but I can't use
the sum function, because I only want to calculate the
totals from the first or last record in the detail
section. The Sum function calculates the running total
for all of the records in the detailed section. I'm
trying to calculat totals on three levels (Source, Year
and grand total). My totals work fine as long as there
in no bage break in my source header or source footer
section. Note the variables in the code are not inclosed
within [] this might help to determin what is a variable
and a control.
-----Original Message-----
I agree with Jeff.
I have created hundreds of reports and have never resorted to accumulating
totals in report code. Is there a reason why you are using code versus
setting a control source to something like:
=Sum([ANumericField])
Also, I find your code hard to read since it isn't clear what is a variable
and what might be a control on the report. I recommend naming your text
boxes like "txtSourceTotal" or whatever. Then, in your code, use
If Me.txtSourceTotal ...
SomeVal = SomeVal + Me.txtSourceTotal
--
Duane Hookom
Microsoft Access MVP


Is there anyway to prevent my totals from messing-up
whenever the totals occur after a page break?

Here is the code that I'm using:

Option Compare Database
'
**********************************************************
' Creating Global variables
'
**********************************************************
Public SourceTotalAwarded As Currency
Public SourceTotalRequested As Currency
Public YearTotalRequested As Currency
Public YearTotalAwarded As Currency
Public GrandTotalRequested As Currency
Public GrandTotalAwarded As Currency

**********************************************************
' Year Footer
'
' Action:
' Accumulating Grand Totals from Yearly Totals
' Assigning Year Totals tp Year Totals fields
' Reseting Yearly Totals to zero
'
**********************************************************
Private Sub GroupFooter1_Format(Cancel As Integer,
FormatCount As Integer)
GrandTotalRequested = GrandTotalRequested +
YearTotalRequested
GrandTotalAwarded = GrandTotalAwarded +
YearTotalAwarded
[Year_Total_Requested] = YearTotalRequested
[Year_Total_Awarded] = YearTotalAwarded
End Sub


**********************************************************
' Year Header
'
' Action:
' Reset Year Totals to zero
'
**********************************************************

Private Sub GroupHeader0_Format(Cancel As Integer,
FormatCount As Integer)
YearTotalAwarded = 0
YearTotalRequested = 0
End Sub

'
**********************************************************
' BUNumber Header
'
' Action:
' Calculate Source Totals
'
**********************************************************
Private Sub GroupHeader1_Format(Cancel As Integer,
FormatCount As Integer)
If [Total_Amount_Awarded] > 0 Then
SourceTotalAwarded = SourceTotalAwarded +
[Total_Amount_Awarded]
End If
If [Total_Amount_Requested] > 0 Then
SourceTotalRequested = SourceTotalRequested +
[Total_Amount_Requested]
End If
End Sub

' ********************************************************
' Open Report
'
' Action:
' Set all variables to zero
'
**********************************************************
Private Sub Report_Open(Cancel As Integer)
SourceTotalAwarded = 0
SourceTotalRequested = 0
YearTotalRequested = 0
YearTotalAwarded = 0
GrandTotalRequested = 0
GrandTotalAwarded = 0
End Sub

'
**********************************************************
' Report Footer
'
' Action:
' Assigning Grand Totals to Gand Totals fields
'
'****************************************************** ***
Private Sub ReportFooter_Format(Cancel As Integer,
FormatCount As Integer)
[Grand_Total_Requested] = GrandTotalRequested
[Grand_Total_Awarded] = GrandTotalAwarded
End Sub

Private Sub ReportHeader_Format(Cancel As Integer,
FormatCount As Integer)
SourceTotalAwarded = 0
SourceTotalRequested = 0
YearTotalRequested = 0
YearTotalAwarded = 0
GrandTotalRequested = 0
GrandTotalAwarded = 0

End Sub


'
**********************************************************
' Source Footer
'
' Action:
' Assign Source Totals to Source Total fields
' Accumulats Yearly Totals from Source Totals
' Reset Source Totals to zero
'
*********************************************************

Private Sub SourceFooter_Print(Cancel As Integer,
PrintCount As Integer)
[Source_Total_Amount_Awarded] = SourceTotalAwarded
YearTotalAwarded = YearTotalAwarded +
[Source_Total_Amount_Awarded]
[Source_Total_Amount_Requested] = SourceTotalRequested
YearTotalRequested = YearTotalRequested +
[Source_Total_Amount_Requested]
SourceTotalAwarded = 0
SourceTotalRequested = 0
End Sub
Click to expand...


.
Click to expand...
 
D

Duane Hookom

You might be able to place a bound text box in your group header or footer
and then set a running sum. The text box in the group header will be the
first record in the next detail section and the group footer will have the
value from the last record in the preceding detail section.
--
Duane Hookom
MS Access MVP


I'm trying to calculate running totlas, but I can't use
the sum function, because I only want to calculate the
totals from the first or last record in the detail
section. The Sum function calculates the running total
for all of the records in the detailed section. I'm
trying to calculat totals on three levels (Source, Year
and grand total). My totals work fine as long as there
in no bage break in my source header or source footer
section. Note the variables in the code are not inclosed
within [] this might help to determin what is a variable
and a control.
-----Original Message-----
I agree with Jeff.
I have created hundreds of reports and have never resorted to accumulating
totals in report code. Is there a reason why you are using code versus
setting a control source to something like:
=Sum([ANumericField])
Also, I find your code hard to read since it isn't clear what is a variable
and what might be a control on the report. I recommend naming your text
boxes like "txtSourceTotal" or whatever. Then, in your code, use
If Me.txtSourceTotal ...
SomeVal = SomeVal + Me.txtSourceTotal
--
Duane Hookom
Microsoft Access MVP


Is there anyway to prevent my totals from messing-up
whenever the totals occur after a page break?

Here is the code that I'm using:

Option Compare Database
'
**********************************************************
' Creating Global variables
'
**********************************************************
Public SourceTotalAwarded As Currency
Public SourceTotalRequested As Currency
Public YearTotalRequested As Currency
Public YearTotalAwarded As Currency
Public GrandTotalRequested As Currency
Public GrandTotalAwarded As Currency

**********************************************************
' Year Footer
'
' Action:
' Accumulating Grand Totals from Yearly Totals
' Assigning Year Totals tp Year Totals fields
' Reseting Yearly Totals to zero
'
**********************************************************
Private Sub GroupFooter1_Format(Cancel As Integer,
FormatCount As Integer)
GrandTotalRequested = GrandTotalRequested +
YearTotalRequested
GrandTotalAwarded = GrandTotalAwarded +
YearTotalAwarded
[Year_Total_Requested] = YearTotalRequested
[Year_Total_Awarded] = YearTotalAwarded
End Sub


**********************************************************
' Year Header
'
' Action:
' Reset Year Totals to zero
'
**********************************************************

Private Sub GroupHeader0_Format(Cancel As Integer,
FormatCount As Integer)
YearTotalAwarded = 0
YearTotalRequested = 0
End Sub

'
**********************************************************
' BUNumber Header
'
' Action:
' Calculate Source Totals
'
**********************************************************
Private Sub GroupHeader1_Format(Cancel As Integer,
FormatCount As Integer)
If [Total_Amount_Awarded] > 0 Then
SourceTotalAwarded = SourceTotalAwarded +
[Total_Amount_Awarded]
End If
If [Total_Amount_Requested] > 0 Then
SourceTotalRequested = SourceTotalRequested +
[Total_Amount_Requested]
End If
End Sub

' ********************************************************
' Open Report
'
' Action:
' Set all variables to zero
'
**********************************************************
Private Sub Report_Open(Cancel As Integer)
SourceTotalAwarded = 0
SourceTotalRequested = 0
YearTotalRequested = 0
YearTotalAwarded = 0
GrandTotalRequested = 0
GrandTotalAwarded = 0
End Sub

'
**********************************************************
' Report Footer
'
' Action:
' Assigning Grand Totals to Gand Totals fields
'
'****************************************************** ***
Private Sub ReportFooter_Format(Cancel As Integer,
FormatCount As Integer)
[Grand_Total_Requested] = GrandTotalRequested
[Grand_Total_Awarded] = GrandTotalAwarded
End Sub

Private Sub ReportHeader_Format(Cancel As Integer,
FormatCount As Integer)
SourceTotalAwarded = 0
SourceTotalRequested = 0
YearTotalRequested = 0
YearTotalAwarded = 0
GrandTotalRequested = 0
GrandTotalAwarded = 0

End Sub


'
**********************************************************
' Source Footer
'
' Action:
' Assign Source Totals to Source Total fields
' Accumulats Yearly Totals from Source Totals
' Reset Source Totals to zero
'
*********************************************************

Private Sub SourceFooter_Print(Cancel As Integer,
PrintCount As Integer)
[Source_Total_Amount_Awarded] = SourceTotalAwarded
YearTotalAwarded = YearTotalAwarded +
[Source_Total_Amount_Awarded]
[Source_Total_Amount_Requested] = SourceTotalRequested
YearTotalRequested = YearTotalRequested +
[Source_Total_Amount_Requested]
SourceTotalAwarded = 0
SourceTotalRequested = 0
End Sub
Click to expand...


.
Click to expand...
Click to expand...
 
G

Guest

-----Original Message-----
Dennis wrote:

I will go further than Jeff and Duane, there is nothing
wrong with your code, except that this approach can not work
reliably. For many reasons, reports do not process their
data sequentially, so event procedures can not accumulate
any kind of a total from one record to another.

As Duane said either use a text box with an aggregate
function, a running sum text box, or a subquery in the
report's record source query.
Click to expand...

That is what I thought that the results well always be
unreliable. Thanks for the advice!

Dennis :blush:)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

totals printing as 0 1
Page Totals Code error 2
Report Totals not adding up corrrectly. 4
Row and Column Averages in Dynamic Crosstab Report? 3
Main Report without Data 1
Page totals error in report - doesn't add correctly 4
Help with an event procedure in a report 3
Dynamate report creation....help 3

Top