Next non weekend/holiday date?

[AkAlan via AccessMonster.com]

I need to create a monthly report that calculates the number of inspections
that were due and the number that were submited on time/late. I was doing
fine until my user told me that if an inspection was due on a weekend or
holiday that it was ok to inspect it on the following non weekend/holiday day.
I created a recordset for the months data and have the following code which
works fine:

While Not rst.EOF

If IsNull(rst![DateInspected]) Or (rst![DateInspected] > rst![DateDue])
Then
intLate = intLate + 1
Else
intOnTime = intOnTime + 1
End If
rst.MoveNext

Wend

Me.txtInspectionLate = intLate
Me.txtInspectionOnTime = intOnTime

I have a table with all the holiday dates and I think I have an idea of how
to loop through and get what I need but was looking for any hints first or
maybe someone has already done this and would share the code. Thanks for any
help.

 

[Jim Burke in Novi]

Something like this should work:

Public Function GetNextWorkingDay(myDate) as date

dim nextDate as date

nextDate = myDate + 1
While IsWeekendOrHoliday(nextDate)
nextDate = nextDate + 1
Wend
GetNextWorkingDay = nextDate

End Function

Public Function IsWeekendOrHoliday(myDate) as boolean

dim dayOfWeek as integer

dayOfWeek = Weekday(myDate)
if IsNull(dlookup("holidayDate","tblHolidayDates","holidayDate = #" & _
myDate & "#")) and dayOfWeek <> 1 and dayOfWeek <> 7 then
IsWeekendOrHoliday = false
else
IsWeekendOrHoliday = true
end if

End Function

I don;t know what your table name is or the fieldname in it so I just made
those up. You should also add error handling.

 

[ruralguy via AccessMonster.com]

Here's a function for a standard module to add WorkDays:

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date

PlusWorkdays = dteStart
Do While intNumDays > 0
PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
' If Weekday(PlusWorkdays, vbMonday) <= 5 Then
'-- If you have a Holiday Table use the next IF instead!
If Weekday(PlusWorkdays, vbMonday) <= 5 And _
IsNull(DLookup("[HoliDate]", "tblHolidays", _
"[HoliDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l")))
Then
'-- The above Format of PlusWorkdays works with US or UK dates!
intNumDays = intNumDays - 1
End If
Loop

End Function

I need to create a monthly report that calculates the number of inspections
that were due and the number that were submited on time/late. I was doing
fine until my user told me that if an inspection was due on a weekend or
holiday that it was ok to inspect it on the following non weekend/holiday day.
I created a recordset for the months data and have the following code which
works fine:

While Not rst.EOF

If IsNull(rst![DateInspected]) Or (rst![DateInspected] > rst![DateDue])
Then
intLate = intLate + 1
Else
intOnTime = intOnTime + 1
End If
rst.MoveNext

Wend

Me.txtInspectionLate = intLate
Me.txtInspectionOnTime = intOnTime

I have a table with all the holiday dates and I think I have an idea of how
to loop through and get what I need but was looking for any hints first or
maybe someone has already done this and would share the code. Thanks for any
help.

 

[AkAlan via AccessMonster.com]

Thanks Jim, I got it to work just fine. Your method was a bit cleaner than
what I had in mind, good job!

Something like this should work:

Public Function GetNextWorkingDay(myDate) as date

dim nextDate as date

nextDate = myDate + 1
While IsWeekendOrHoliday(nextDate)
nextDate = nextDate + 1
Wend
GetNextWorkingDay = nextDate

End Function

Public Function IsWeekendOrHoliday(myDate) as boolean

dim dayOfWeek as integer

dayOfWeek = Weekday(myDate)
if IsNull(dlookup("holidayDate","tblHolidayDates","holidayDate = #" & _
myDate & "#")) and dayOfWeek <> 1 and dayOfWeek <> 7 then
IsWeekendOrHoliday = false
else
IsWeekendOrHoliday = true
end if

End Function

I don;t know what your table name is or the fieldname in it so I just made
those up. You should also add error handling.

I need to create a monthly report that calculates the number of inspections
that were due and the number that were submited on time/late. I was doing

[quoted text clipped - 22 lines]

maybe someone has already done this and would share the code. Thanks for any
help.