calculate next business day excluding holidays

Public Sub MakeDates(pTable As String, pYear As String, pDays As String)
'******************************************
'Purpose:   Create/populate table with specified workdays
'           (2 = Monday, 6 = Friday) of specified pYear,
'           excluding holidays in tblHolidates2
'Coded by:  raskew
'Inputs:    Call MakeDates("tblDates", "2008", "23456") '2 = Monday
'******************************************
Dim db      As Database
Dim rs      As Recordset
Dim rs2     As Recordset
Dim dteHold As Date
Dim dteEnd  As Date
Dim n       As Integer
Dim strName As String

On Error Resume Next

dteHold = DateSerial(pYear, 1, 1)
dteEnd = DateSerial(pYear, 12, 31)

Set db = CurrentDb
Set rs2 = db.OpenRecordset("tblHolidates2", dbOpenDynaset)

'Does table pTable exist?  If true, delete it;
   strName = pTable
   db.Execute "DROP TABLE " & strName & ";"
'Create new tblTable
    db.Execute "CREATE TABLE " & strName & " (DteID AUTOINCREMENT, dteDay DATETIME, dteWeekday TEXT (10));"

    Set rs = db.OpenRecordset(strName)

    'populate table
    Do While dteHold <= dteEnd
       If InStr(pDays, WeekDay(dteHold)) > 0 Then
          rs2.FindFirst "Holidate = #" & dteHold & "#"
          If rs2.NoMatch Then
             With rs
                .AddNew
                !dteDay = dteHold
                !dteWeekday = Format(dteHold, "dddd")
                .Update
             End With
          End If
       End If
       dteHold = dteHold + 1
   Loop
   db.TableDefs.Refresh
'  cleanup
   rs.Close
   rs2.Close
   db.Close
   Set db = Nothing

End Sub

Function GetBusinessDay(FromDate As Date, Offset As Long, Optional WorkDays As String = "23456", _
    Optional ZeroOffsetBehavior As Long = 1, Optional HolidayTblName As String = "", _
    Optional HolidayDateField As String = "")
    
    Dim DaysCounter As Long
    Dim TestDate As Date
    Dim Holidays As String
    Dim rs As DAO.Recordset
    
    FromDate = DateValue(FromDate)
    
    If HolidayTblName <> "" Then
        If Left(HolidayTblName, 1) <> "[" Then HolidayTblName = "[" & HolidayTblName & "]"
        If Left(HolidayDateField, 1) <> "[" Then HolidayDateField = "[" & HolidayDateField & "]"
        Set rs = CurrentDb.OpenRecordset("SELECT " & HolidayDateField & " FROM " & HolidayTblName)
        Do Until rs.EOF
            Holidays = Holidays & Format(rs.Fields(0).Value, "|yyyy-mm-dd|")
            rs.MoveNext
        Loop
        rs.Close
        Set rs = Nothing
    End If
    
    Select Case Offset
        Case 0
            If InStr(1, WorkDays, Weekday(FromDate)) > 0 And _
                InStr(1, Holidays, Format(FromDate, "|yyyy-mm-dd|")) = 0 Then
                GetBusinessDay = FromDate
            ElseIf ZeroOffsetBehavior = 0 Then
                GetBusinessDay = FromDate
            ElseIf ZeroOffsetBehavior < 0 Then
                TestDate = FromDate
                Do Until InStr(1, WorkDays, Weekday(TestDate)) > 0 And _
                    InStr(1, Holidays, Format(TestDate, "|yyyy-mm-dd|")) = 0
                    TestDate = DateAdd("d", -1, TestDate)
                Loop
                GetBusinessDay = TestDate
            Else
                TestDate = FromDate
                Do Until InStr(1, WorkDays, Weekday(TestDate)) > 0 And _
                    InStr(1, Holidays, Format(TestDate, "|yyyy-mm-dd|")) = 0
                    TestDate = DateAdd("d", 1, TestDate)
                Loop
                GetBusinessDay = TestDate
            End If
        Case Is > 0
            TestDate = FromDate
            Do Until DaysCounter = Offset
                TestDate = DateAdd("d", 1, TestDate)
                If InStr(1, WorkDays, Weekday(TestDate)) > 0 And _
                    InStr(1, Holidays, Format(TestDate, "|yyyy-mm-dd|")) = 0 Then
                    DaysCounter = DaysCounter + 1
                End If
            Loop
            GetBusinessDay = TestDate
        Case Else
            TestDate = FromDate
            Do Until DaysCounter = Offset
                TestDate = DateAdd("d", -1, TestDate)
                If InStr(1, WorkDays, Weekday(TestDate)) > 0 And _
                    InStr(1, Holidays, Format(TestDate, "|yyyy-mm-dd|")) = 0 Then
                    DaysCounter = DaysCounter - 1
                End If
            Loop
            GetBusinessDay = TestDate
    End Select
        
End Function