Option Compare Database 
Option Explicit 
 
Public Function fModBusDay(ByVal dDay As Date) As Date 
     'Returns days to add to specified date depending
     'if day selected is a weekend or exists in tbl_holidays
    Dim stSQL As String 
    Dim rst As ADODB.Recordset 
    Dim lAdd As Long 
    Dim dHol As Date 
     
     'first we get to the monday if dDay is Sat or Sun
TestWeekDay: 
    Select Case Weekday(dDay, vbMonday) 
    Case 1 To 5: lAdd = 0 
    Case Is = 6: lAdd = 2 
    Case Is = 7: lAdd = 1 
    End Select 
    dDay = DateAdd("d", lAdd, dDay) 
     'then we check to see if the revised date is a holiday
    stSQL = "SELECT HolDate FROM tbl_Holidays WHERE HolDate = #" & dDay & "#" 
    Set rst = CurrentProject.Connection.Execute(stSQL, , adCmdText) 
     'i.e. if a record is returned
    If Not rst.BOF Then 
        dHol = rst(0) 
        rst.Close 
    End If 
     'then check if dday is a holiday
    If dHol = dDay Then 'if so add another day and re-test
        dDay = DateAdd("d", 1, dDay) 
        Goto TestWeekDay 
    Else ' if not, we have a valid day & can exit
        fModBusDay = dDay 
        Goto ExitHere 
    End If 
ExitHere: 
End Function