Hi,
I have a query which I am trying to calculate 15 business days in the future. I dont have a problem doing this in Excel, but it is a nightmare in Access. I called our helpdesk who gave me the function below, but it is not working. I hope you can help. Thanks.
Public Function WorkDayDate(TheDate As Date, ByVal DaysIncrement As Long, _
Optional IncludeHolidays As Boolean) As Variant
'*************************************************************
'Purpose: Returns date number of workdays in the
' past or future specified by DaysIncrement
'Arguments: TheDate - any valid date, or date field reference
' DaysIncrement - number of workdays from "TheDate"
' *Use negative number for past
'Returns: A Date or Null(if an error occured)
'*************************************************************
Const SUNDAY = 1
Const SATURDAY = 7
Dim EndDate As Date
Dim i As Long, j As Long
On Error GoTo err_WorkDayDate
If DaysIncrement < 0 Then
'go backwards (in the past)
i = -1
Else
'go forward (in the future date)
i = 1
End If
j = 1
EndDate = TheDate
Do Until j = Abs(DaysIncrement) 'number of workdays
If WeekDay(EndDate) <> SUNDAY And WeekDay(EndDate) <> SATURDAY Then
'since it is workday then check to see if it is a holiday
If IncludeHolidays = True Then
If IsNull(DLookup("HolidayDate", "tblHolidays", _
"[HolidayDate] = #" & EndDate & "#")) Then
'since it is not a holiday include another workday
j = j + 1
Else
End If
Else
'not worrying about holidays so include one more workday
j = j + 1
End If
Else
End If
EndDate = EndDate + i
Loop
WorkDayDate = EndDate
Exit Function
err_WorkDayDate:
WorkDayDate = Null
End Function
I have a query which I am trying to calculate 15 business days in the future. I dont have a problem doing this in Excel, but it is a nightmare in Access. I called our helpdesk who gave me the function below, but it is not working. I hope you can help. Thanks.
Public Function WorkDayDate(TheDate As Date, ByVal DaysIncrement As Long, _
Optional IncludeHolidays As Boolean) As Variant
'*************************************************************
'Purpose: Returns date number of workdays in the
' past or future specified by DaysIncrement
'Arguments: TheDate - any valid date, or date field reference
' DaysIncrement - number of workdays from "TheDate"
' *Use negative number for past
'Returns: A Date or Null(if an error occured)
'*************************************************************
Const SUNDAY = 1
Const SATURDAY = 7
Dim EndDate As Date
Dim i As Long, j As Long
On Error GoTo err_WorkDayDate
If DaysIncrement < 0 Then
'go backwards (in the past)
i = -1
Else
'go forward (in the future date)
i = 1
End If
j = 1
EndDate = TheDate
Do Until j = Abs(DaysIncrement) 'number of workdays
If WeekDay(EndDate) <> SUNDAY And WeekDay(EndDate) <> SATURDAY Then
'since it is workday then check to see if it is a holiday
If IncludeHolidays = True Then
If IsNull(DLookup("HolidayDate", "tblHolidays", _
"[HolidayDate] = #" & EndDate & "#")) Then
'since it is not a holiday include another workday
j = j + 1
Else
End If
Else
'not worrying about holidays so include one more workday
j = j + 1
End If
Else
End If
EndDate = EndDate + i
Loop
WorkDayDate = EndDate
Exit Function
err_WorkDayDate:
WorkDayDate = Null
End Function
