Good morning/afternoon,
I have a module that calculates future dates from a start date and I need to figure out how to add subtraction part to it. So, the same idea, start date minus 10 business days (calling is in a query), if falls on a holiday, then the next business day is the DATE. I tried several things, it just doesn’t work. I am not a programmer, just self-taught… I am working in 2016 Access. The code is below. Any help will be much appreciated!!
I have a module that calculates future dates from a start date and I need to figure out how to add subtraction part to it. So, the same idea, start date minus 10 business days (calling is in a query), if falls on a holiday, then the next business day is the DATE. I tried several things, it just doesn’t work. I am not a programmer, just self-taught… I am working in 2016 Access. The code is below. Any help will be much appreciated!!
Code:
Public Function AddWeekdays(dteStartDate As Date, lngNumOfDays As Long)
Dim lngCount As Long
Dim lngCtr As Long
Dim dteDate As Date
lngCount = 0
lngCtr = 1
Debug.Print "Date", "Day Count", "Weekday"
Do
dteDate = DateAdd("d", lngCtr, dteStartDate)
Select Case Weekday(dteDate)
Case 7, 1 'Saturday and Sunday, do nothing
Case Else 'Monday thru Friday, OK
'Is it a Holiday as posted in tblHolidays?
If DCount("*", "tblHolidays", "[HolidayDate] = #" & dteDate & "#") < 1 Then 'NOT Holiday
lngCount = lngCount + 1 'Increment Counter
Debug.Print dteDate, lngCount, Weekday(dteDate)
End If
End Select
lngCtr = lngCtr + 1
Loop While lngCount < lngNumOfDays
AddWeekdays = dteDate
End Function
Last edited by a moderator: