So I wrote this neat little function that will return a date without a holiday table, and it works great, but now I'm being asked to exclude the holidays. I have a form that filters 3 append queries and generates a couple of reports.
Here's my code, in case anyone needs it. Basically when you click command7 it populates today's date [todate] and the populates 3 other fields, with dates that are today minus, 2,3,10. Then reports and queries are run accordingly. Anyone offer any tips on how to have the same outcome but using a holidaytbl? I can figure out how to get it to return a number, meaning the amount to networkdays between the two dates, but I cant get it to return the actual dates, obviously not including the dates in my table. Thank you in advance.
CODE
Function Calculate_Difference_in_workdays(NoofDays As Integer, LateDate As Date)
Dim EarlyDate As Date
Dim NoofSats, NoofSuns As Integer
Dim Excep1 As Integer
NoofSuns = 0
NoofSats = 0
Excep1 = 0
If NoofDays = 2 And WeekdayName(Weekday(LateDate)) = "Monday" Then
Excep1 = Excep1 + 1
End If
For i = 0 To NoofDays - 1
If WeekdayName(Weekday(LateDate - i)) = "Sunday" Then
NoofSuns = NoofSuns + 1
ElseIf WeekdayName(Weekday(LateDate - i)) = "Saturday" Then
NoofSats = NoofSats + 1
End If
Next
EarlyDate = LateDate - (NoofDays + NoofSuns + NoofSats + Excep1)
'MsgBox EarlyDate
If WeekdayName(Weekday(EarlyDate)) = "Sunday" Then
EarlyDate = EarlyDate - 2
ElseIf WeekdayName(Weekday(EarlyDate)) = "Saturday" Then
EarlyDate = EarlyDate - 1
Else
EarlyDate = EarlyDate - 0
End If
Calculate_Difference_in_workdays = EarlyDate
End Function
Dim EarlyDate As Date
Dim NoofSats, NoofSuns As Integer
Dim Excep1 As Integer
NoofSuns = 0
NoofSats = 0
Excep1 = 0
If NoofDays = 2 And WeekdayName(Weekday(LateDate)) = "Monday" Then
Excep1 = Excep1 + 1
End If
For i = 0 To NoofDays - 1
If WeekdayName(Weekday(LateDate - i)) = "Sunday" Then
NoofSuns = NoofSuns + 1
ElseIf WeekdayName(Weekday(LateDate - i)) = "Saturday" Then
NoofSats = NoofSats + 1
End If
Next
EarlyDate = LateDate - (NoofDays + NoofSuns + NoofSats + Excep1)
'MsgBox EarlyDate
If WeekdayName(Weekday(EarlyDate)) = "Sunday" Then
EarlyDate = EarlyDate - 2
ElseIf WeekdayName(Weekday(EarlyDate)) = "Saturday" Then
EarlyDate = EarlyDate - 1
Else
EarlyDate = EarlyDate - 0
End If
Calculate_Difference_in_workdays = EarlyDate
End Function
'It can then be called like this
CODE
Private Sub Command7_Click()
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim intLoop As Integer
Set dbCurr = CurrentDb()
For intLoop = (dbCurr.TableDefs.count - 1) To 0 Step -1
Set tdfCurr = dbCurr.TableDefs(intLoop)
If InStr(tdfCurr.Name, "ImportError") > 0 Then
dbCurr.TableDefs.Delete tdfCurr.Name
End If
Next intLoop
Set dbCurr = Nothing
Me.todate = Date
Me.fromdate = Calculate_Difference_in_workdays(3, CDate(todate))
Me.Threeday = Calculate_Difference_in_workdays(4, CDate(todate))
Me.TenDay = Calculate_Difference_in_workdays(11, CDate(todate))
Forms!dates!Command0.SetFocus
'Me.Command7.Enabled = False
End Sub
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim intLoop As Integer
Set dbCurr = CurrentDb()
For intLoop = (dbCurr.TableDefs.count - 1) To 0 Step -1
Set tdfCurr = dbCurr.TableDefs(intLoop)
If InStr(tdfCurr.Name, "ImportError") > 0 Then
dbCurr.TableDefs.Delete tdfCurr.Name
End If
Next intLoop
Set dbCurr = Nothing
Me.todate = Date
Me.fromdate = Calculate_Difference_in_workdays(3, CDate(todate))
Me.Threeday = Calculate_Difference_in_workdays(4, CDate(todate))
Me.TenDay = Calculate_Difference_in_workdays(11, CDate(todate))
Forms!dates!Command0.SetFocus
'Me.Command7.Enabled = False
End Sub
