Cycling through an Access recordset provides an incorrect count

Question

I've added VBA code (below) to cycle through the results of an Access query. The intent of this code is to count the number of times 500 appears in a specific column. Once it hits 500, it grabs the time and adds 30 minutes. The client doesn't want any 500 counted again in they appear in a 30 minute window.

This works well until the last time is after 11:30 PM. After 11:30 PM, the "MyEnd" variable gets set to an AM time on the next day. It exits the loop and then counts the next 500; which throws my actual count off my 1.

I could subtract 1 from the count but I don't know if this will always be true. Their could be multiple 500s and the count would be off as well.

I trying to determine how to handle the last 2 times the code finds 500 after 11:30 PM.

Any suggestions would be greatly appreciated. Thanks for your help.....

Dim strSQL As String
Dim rs As DAO.Recordset
Dim MyStart As Date
Dim MyEnd As Date
Dim CntA As Integer
    
CntA = 0
MyStart = 0
    
strSQL = "SELECT * FROM FurDet ORDER BY FurDet.Time"
        
Set rs = CurrentDb.OpenRecordset(strSQL)
    
If Not rs.BOF And Not rs.EOF Then
        
     rs.MoveFirst
            
     While (Not rs.EOF)
        
          DoEvents
                    
          If rs.Fields("503D") = 500 Then
                
               MyStart = TimeValue(rs.Fields("Time"))
               MyEnd = TimeValue(MyStart + "0:30")
                
               CntA = CntA + 1
                
               Do Until TimeValue(rs.Fields("Time")) > TimeValue(MyEnd) Or rs.EOF = True                        
                    DoEvents                        
                    rs.MoveNext                        
                    If rs.EOF = True Then Exit Do                        
               Loop
                
          End If
            
          If rs.EOF = True Then
'               do nothing
          Else
              rs.MoveNext
          End If
                    
     Wend
        
End If
   
Debug.Print CntA

Answer 1

You should have stored your time as Date, not text. Anyway, try this, including the date:

Dim StartDate   As Date
Dim MyTime      As Date

' <snip>

StartDate = Date

rs.MoveFirst            
While Not rs.EOF
    If rs.Fields("503D").Value = 500 Then                
        MyStart = StartDate + TimeValue(rs.Fields("Time"))
        MyEnd = DateAdd("n", 30, MyStart)                
        CntA = CntA + 1       
        Do
            MyTime = TimeValue(rs.Fields("Time").Value)
            rs.MoveNext                        
        Loop Until DateDiff("n", MyStart + MyTime, MyEnd) > 0 Or rs.EOF
    End If
    rs.MoveNext
Wend
rs.Close