Automating Suspenses

[Rick]

Hi, I have a form that automatically calculates a suspense
of two days but here is the problem, what if the suspense
date falls on a weekend or a holiday? Does anyone know
how I can write code to take the weekends into
consideration when the form calculates the two-day
suspense? If it isn't too difficult, can I add holidays
into the pot and modify suspense due dates for those as
well without having to create a new table for the holidays?

The current code I have is pretty simple:

Me.Suspense = Me.Notification + 2

 

[Matt Weyland]

Paste this code into a new module then use the functions
to return whether holiday or weekend is set. This code
is more or less the substitute for the Excel function of
networkdays. Hope this helps. If you have any questions
just let me know.

Matt Weyland
mweyland at mnqio dot sdps dot org

'**********************************************************
Public Function NumBusinessDays(dteDateBegin As Date,
dteDateEnd As Date) As Integer ' Number of Business Days
between dates
Dim dteTheDate As Date

NumBusinessDays = 0
dteTheDate = dteDateBegin

Do Until dteDateEnd < dteTheDate
dteTheDate = TheNextBusinessDayDate(dteTheDate)
If dteDateEnd < dteTheDate Then
Exit Function
End If
NumBusinessDays = NumBusinessDays + 1
Loop

End Function
Public Function TheNextBusinessDayDate(dteDate As Date) As
Date ' Gets the next Business Day date
Dim dteTheDate As Date

dteTheDate = DateAdd("d", 1, Format
(dteDate, "mm/dd/yyyy"))

JustDoIt:

Do Until IsAHoliday(dteTheDate) = False
dteTheDate = DateAdd("d", 1, dteTheDate)
Loop

Do Until IsAWeekendDate(dteTheDate) = False
dteTheDate = DateAdd("d", 1, dteTheDate)
Loop

If IsAHoliday(dteTheDate) Then
dteTheDate = DateAdd("d", 1, dteTheDate)
GoTo JustDoIt
End If

TheNextBusinessDayDate = dteTheDate

End Function
Public Function IsAHoliday(dteDate As Date) As Boolean '
Determines if a date is a holiday
Dim db As Database
Dim rsHoliday As Recordset

Set db = CurrentDb()
Set rsHoliday = db.OpenRecordset("SELECT Date FROM
tblHolidays WHERE Date=#" & dteDate & "#;")

IsAHoliday = False

With rsHoliday
If .EOF Then
Exit Function
Else
IsAHoliday = True
End If
End With

rsHoliday.Close
Set rsHoliday = Nothing

End Function
Public Function IsAWeekendDate(dteDate As Date) As
Boolean ' Determines if a date falls on a weekend

IsAWeekendDate = False

If Weekday(dteDate) = vbSaturday Or Weekday(dteDate) =
vbSunday Then
IsAWeekendDate = True
End If

End Function

Public Function addWorkDays(dteDate As Date, numDays As
Integer) As Date
Dim dteTheDate As Date
Dim days As Integer
days = 0
dteTheDate = dteDate
Do Until days = numDays
dteTheDate = TheNextBusinessDayDate(dteTheDate)
days = days + 1
Loop
addWorkDays = dteTheDate

End Function