Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
If rs_SixtyDayList.EOF = False Then
rs_SixtyDayList.MoveNext
MyDate = rs_SixtyDayList!WorkDate
ElseIf rs_SixtyDayList.EOF = True Then
Exit Function
End If
' Procedure : GetSixtyDayCount
' Author : Jim
' Date : 10/23/2013
' Purpose : Determines the number of days working by getting the Start and Dates from table as opposed to calculating days between two dates.
' These dates are not consecutive.
'
'---------------------------------------------------------------------------------------
'
Public Function GetSixtyDayCount() As Integer
Dim SixtyDayStart As Date
Dim SixtyDayEnd As Date
Dim Dbas As DAO.Database
Dim rs_SixtyDayList As DAO.Recordset
Set Dbas = CurrentDb
Set rs_SixtyDayList = Dbas.OpenRecordset("SELECT tblHours.WorkDate" & _
" FROM tblHours" & _
" GROUP BY tblHours.WorkDate" & _
" HAVING (((tblHours.WorkDate) >= #6/26/2013#))" & _
" ORDER BY tblHours.WorkDate;", dbOpenDynaset)
With rs_SixtyDayList
.MoveLast
SixtyDayEnd = rs_SixtyDayList!WorkDate
.MoveFirst
SixtyDayStart = rs_SixtyDayList!WorkDate
End With
'Counts number of Days between two dates not counting holidays if they are in the table.
'Does Not count Saturday or Sunday.
Dim dbs As DAO.Database
Dim rstHolidays As DAO.Recordset
Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)
Dim Idx As Long
Dim MyDate As Date
Dim NumDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1
Dim StartDate As Date
Dim EndDate As Date
NumSgn = VBA.Chr(35)
MyDate = VBA.Format(SixtyDayStart, "Short date")
Do While Not rs_SixtyDayList.EOF
For Idx = CLng(SixtyDayStart) To CLng(SixtyDayEnd)
Select Case (Weekday(MyDate))
Case Is = 1 'Sunday
'Do Nothing, it is NOT a Workday
Case Is = 7 'Saturday
'Do Nothing, it is NOT a Workday
Case 1 To 7 'Normal Workday
strCriteria = "[Holidate] = " & NumSgn & VBA.Format$(MyDate, "yyyy-mm-dd") & NumSgn 'Thanks to "RoyVidar" 2/18/04
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
NumDays = NumDays + 1
Else
'Do Nothing, it is NOT a Workday
End If
End Select
If MyDate <> SixtyDayEnd Then
rs_SixtyDayList.MoveNext
MyDate = rs_SixtyDayList!WorkDate
Else
Exit Do
End If
Next Idx
Loop
GetSixtyDayCount = NumDays
rs_SixtyDayList.Close
Set rs_SixtyDayList = Nothing
Set Dbas = Nothing
rstHolidays.Close
Set rstHolidays = Nothing
Set dbs = Nothing
End Function