Can someone help me with a sub procedure that doesn’t always calculate properly.
I have a table named tblRecap that has daily profit and loss figures.
A query named qryMonthlySums that totals these daily figures for each month.
A form named frmGetReports where the user enters a year for a report to compile.
A report named rptVBATest that lists the sum of these monthly figures as profits or losses.
The code is suppose to enter a value in an unbound text control on the rptVBATest report that shows how many consecutive months that a profit was made and conversely how many consecutive months that a loss was made.
The code executes properly and gives the proper results for all years except
1993
2001
2004
Here is the code:
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim ConsqWin As Long
Dim ConsqLoss As Long
Dim tmpWin As Long
Dim tmpLoss As Long
'Note Yr is a number data type in the table
'and not a year data type.
Dim txtYr As Integer
Dim txtEvent As String
Dim Rs As DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryMonthlySums")
qdf.Parameters(0) = [Forms]![frmGetReports]![Yr]
Set Rs = qdf.OpenRecordset
Do While Not Rs.EOF
Do While Rs!SumOfNet > 1
tmpWin = tmpWin + 1
Rs.MoveNext
If Rs.EOF Then Exit Do
Loop
If Rs.EOF Then Exit Do
If tmpWin > ConsqWin Then ConsqWin = tmpWin
tmpWin = 0
Rs.MoveNext
Loop
Rs.Close
qdf.Close
Set Rs = qdf.OpenRecordset
Do While Not Rs.EOF
Do While Rs!SumOfNet < 1
tmpLoss = tmpLoss + 1
Rs.MoveNext
If Rs.EOF Then Exit Do
Loop
If Rs.EOF Then Exit Do
If tmpLoss > ConsqLoss Then ConsqLoss = tmpLoss
tmpLoss = 0
Rs.MoveNext
Loop
Me.txtConsecMW.Value = ConsqWin
Me.txtConsecML.Value = ConsqLoss
Rs.Close
qdf.Close
Set Rs = Nothing
End Sub
I’ve attached the database and perhaps someone can review it and see what I am missing.
I have worked on this for 3 days and just don’t see me solving the issue. It’s probably something right in front of my face, but I can’t figure it out. Any help would be greatly appreciated.
I have a table named tblRecap that has daily profit and loss figures.
A query named qryMonthlySums that totals these daily figures for each month.
A form named frmGetReports where the user enters a year for a report to compile.
A report named rptVBATest that lists the sum of these monthly figures as profits or losses.
The code is suppose to enter a value in an unbound text control on the rptVBATest report that shows how many consecutive months that a profit was made and conversely how many consecutive months that a loss was made.
The code executes properly and gives the proper results for all years except
1993
2001
2004
Here is the code:
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim ConsqWin As Long
Dim ConsqLoss As Long
Dim tmpWin As Long
Dim tmpLoss As Long
'Note Yr is a number data type in the table
'and not a year data type.
Dim txtYr As Integer
Dim txtEvent As String
Dim Rs As DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryMonthlySums")
qdf.Parameters(0) = [Forms]![frmGetReports]![Yr]
Set Rs = qdf.OpenRecordset
Do While Not Rs.EOF
Do While Rs!SumOfNet > 1
tmpWin = tmpWin + 1
Rs.MoveNext
If Rs.EOF Then Exit Do
Loop
If Rs.EOF Then Exit Do
If tmpWin > ConsqWin Then ConsqWin = tmpWin
tmpWin = 0
Rs.MoveNext
Loop
Rs.Close
qdf.Close
Set Rs = qdf.OpenRecordset
Do While Not Rs.EOF
Do While Rs!SumOfNet < 1
tmpLoss = tmpLoss + 1
Rs.MoveNext
If Rs.EOF Then Exit Do
Loop
If Rs.EOF Then Exit Do
If tmpLoss > ConsqLoss Then ConsqLoss = tmpLoss
tmpLoss = 0
Rs.MoveNext
Loop
Me.txtConsecMW.Value = ConsqWin
Me.txtConsecML.Value = ConsqLoss
Rs.Close
qdf.Close
Set Rs = Nothing
End Sub
I’ve attached the database and perhaps someone can review it and see what I am missing.
I have worked on this for 3 days and just don’t see me solving the issue. It’s probably something right in front of my face, but I can’t figure it out. Any help would be greatly appreciated.
