Full Version: Returning A Work Date
pensived
Hello Everyone,
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

'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

theDBguy
Hi,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

Is it possible that you could use Brent's function in the Code Archive that does the same thing?

Just my 2 cents...
Gustav
Here's a method:
Calculate Future Date
/gustav
pensived
Worked perfectly! Thank you Thank you!

Called if from the form, easy peasy lemon squeezy!!