Count consecutive records that match criteria (1 Viewer)

[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.

 

[x0reset]

Your talking about doing calculations between records instead of between fields. Tough to do! This is the ONE situation where I have found it necessary to store a calculated field.

My solution:

1. Add a field to your table, call it ConsWeeks or something apt.

2. Create a new form, set the "Data Source" to your table.

3. In the "On Open" event of the form add code that looks something like:

Private Sub Form_Open(Cancel As Integer)

Dim RecordCount As Integer
Dim Weeks As Integer

Weeks = 0
RecordCount = Me.RecordsetClone.RecordCount - 1

DoCmd.GoToRecord acDataForm, "MyForm", acLast
DoCmd.GoToRecord acDataForm, "MyForm", acFirst

For Count = 0 To RecordCount
If Me.Net > 0 Then
Weeks = Weeks + 1
Me.ConsWeeks = Weeks
Else:
Weeks = 0
Me.ConsWeeks = Weeks
End If
DoCmd.GoToRecord , , acNext
Next Count

Me.Close

End Sub

4. Add the following code to the "On Close" event of the form:

Private Sub Form_Close()

DoCmd.OpenReport MyReport

End Sub

This will automatically calculate and store consecutive weeks when the form is opened. Note the form closes before you ever see it (all you see is an hourglass during calculations), and automatically opens the report after finishing calculations. At this point, you just need to set the control source of your report control to the ConsWeek field. Notice that using this method requires you to open the form before the report to get the necessary calculations.