Count consecutive records that match criteria 1

[theSizz]

I want to place a control in a report footer that will return the highest number of consecutive weeks that a profit was earned. The database has fields named WeekNo and Net. The Net field contains positive values (profit) and negative values (losses). The WeekNo field is an integer from 1 to 52 designating the week number.

For example if the figure in the Net field is a positive number I want the control to count the number of consecutive weeks that a profit was earned. I know how to get the value of total weeks that a profit was earned; this is not what I am looking for. The control must return the highest number of CONSECUTIVE weeks that a profit was earned over the year.

Any help would be appreciated.

 

[Remou]

Here is a rough idea. I am not sure how you want to treat 0. I have assumed that your report is ordered by week. I hope I have understood what you wish to do.

Option Compare Database
Option Explicit
Dim WeeksNet
Dim TempNet

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.[Net] > 0 Then
    TempNet = TempNet + 1
    If TempNet > WeeksNet Then
        WeeksNet = TempNet 
    End If
Else
    TempNet = 0
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
WeeksNet = 0
TempNet = 0
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Me.MaxWeeksInProfit = WeeksNet 
End Sub

 

[theSizz]

Thanks Remou, your solution works perfect.

One word of caution to others who might use this code. In order to achieve the correct results make sure you sort the date field at the report level. Even if your date field is sorted in your table or query (whatever your source may be), unless you sort the field in the report you won't get the expected results.

Once again Remou thank you.