Public Function GetAbsInRange(ClockNumber As String, Optional StartPeriod As Date = 0, Optional EndPeriod As Date = 0)
Dim rs As DAO.Recordset
Dim strSql As String
Dim absPeriod As Integer
Dim prev As String
If StartPeriod = 0 And EndPeriod = 0 Then 'Include all records
strSql = "Select * from Occurences where clockID = '" & ClockNumber & "' order by WorkDate"
ElseIf StartPeriod = 0 And EndPeriod <> 0 Then 'Include all records <= endperiod
strSql = "Select * from Occurences where clockID = '" & ClockNumber & "' AND WorkDate <= #" & Format(EndPeriod, "mm/dd/yyyy") & "# order by WorkDate"
ElseIf StartPeriod <> 0 And EndPeriod = 0 Then 'Include all records >= to startPeiod
strSql = "Select * from Occurences where clockID = '" & ClockNumber & "' AND WorkDate >= #" & Format(StartPeriod, "mm/dd/yyyy") & "# order by WorkDate"
Else 'Include all records between startPeiod and EndPeriod
strSql = "Select * from Occurences where clockID = '" & ClockNumber & "' AND WorkDate between #" & Format(StartPeriod, "mm/dd/yyyy") _
& "# AND #" & Format(EndPeriod, "mm/dd/yyyy") & "# order by WorkDate"
End If
Set rs = CurrentDb.OpenRecordset(strSql)
'Debug.Print strSql
Do While Not rs.EOF
'Debug.Print rs!WorkType
If prev <> rs!WorkType And rs!WorkType = "ABS" Then
absPeriod = absPeriod + 1
End If
prev = rs!WorkType
rs.MoveNext
Loop
GetAbsInRange = absPeriod
End Function
Public Sub TestPeriod()
Debug.Print GetAbsInRange("002002", #4/1/2019#, #5/1/2019#)
Debug.Print GetAbsInRange("002002", #4/1/2019#)
Debug.Print GetAbsInRange("002002", , #5/28/2019#)
Debug.Print GetAbsInRange("002002")
End Sub