C
Christina Wise via AccessMonster.com
I am trying to create a module that calculates business days in the future.
In other words, given a date, calculate the business date say five days in
the future, excluding holidays and weekends.
Per the instructions at Arvin Meyer’s site
http://www.datastrat.com/Code/GetBusinessDay.txt , I created a table
“Holidays” that contains a column “HolidayDate” listing the holidays.
I then copied the code for the function into a module. But when I try to run
the debugger it gets caught up on the line
Dim rst As DAO.Recordset
And tells me “Compile error: User defined type not defined”. I’m not sure
what I need to rename or change to get this to work.
I am using Access2000. I tried going into “References” on the “Tools” menu
but was not sure which DAO library to select or if this was even what was
causing the problemThe options were:
Microsoft DAO 2.5/3.5 Compatibility Library
Microsoft DAO 3.51 Object Library
Microsoft DAO 3.6 Object Library
I realize there’s probably something very straightforward that I’m missing,
but I’m not very efficient with VB. Any help would be greatly appreciated.
The module/function code I’m trying to run is as follows:
Function GetBusinessDay(datStart As Date, intDayAdd As Integer)
On Error GoTo Error_Handler
'Adds/Subtracts the proper Business day skipping holidays and weekends
'Requires a table (tblHolidays) with a date field (HolidayDate)
'Arvin Meyer 05/26/98 revised 3/12/2002
'© Arvin Meyer 1998 - 2002 You may use this code in your application provided
author
' is given credit. This code may not be distributed as part of a collection
' without prior written permission. This header must remain intact.
Dim rst As DAO.Recordset
Dim DB As DAO.Database
'Dim strSQL As String
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)
If intDayAdd > 0 Then
Do While intDayAdd > 0
datStart = datStart + 1
rst.FindFirst "[HolidayDate] = #" & datStart & "#"
If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday
Then
If rst.NoMatch Then intDayAdd = intDayAdd - 1
End If
Loop
ElseIf intDayAdd < 0 Then
Do While intDayAdd < 0
datStart = datStart - 1
rst.FindFirst "[HolidayDate] = #" & datStart & "#"
If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday
Then
If rst.NoMatch Then intDayAdd = intDayAdd + 1
End If
Loop
End If
GetBusinessDay = datStart
Exit_Here:
rst.Close
Set rst = Nothing
Set DB = Nothing
Exit Function
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Function
In other words, given a date, calculate the business date say five days in
the future, excluding holidays and weekends.
Per the instructions at Arvin Meyer’s site
http://www.datastrat.com/Code/GetBusinessDay.txt , I created a table
“Holidays” that contains a column “HolidayDate” listing the holidays.
I then copied the code for the function into a module. But when I try to run
the debugger it gets caught up on the line
Dim rst As DAO.Recordset
And tells me “Compile error: User defined type not defined”. I’m not sure
what I need to rename or change to get this to work.
I am using Access2000. I tried going into “References” on the “Tools” menu
but was not sure which DAO library to select or if this was even what was
causing the problemThe options were:
Microsoft DAO 2.5/3.5 Compatibility Library
Microsoft DAO 3.51 Object Library
Microsoft DAO 3.6 Object Library
I realize there’s probably something very straightforward that I’m missing,
but I’m not very efficient with VB. Any help would be greatly appreciated.
The module/function code I’m trying to run is as follows:
Function GetBusinessDay(datStart As Date, intDayAdd As Integer)
On Error GoTo Error_Handler
'Adds/Subtracts the proper Business day skipping holidays and weekends
'Requires a table (tblHolidays) with a date field (HolidayDate)
'Arvin Meyer 05/26/98 revised 3/12/2002
'© Arvin Meyer 1998 - 2002 You may use this code in your application provided
author
' is given credit. This code may not be distributed as part of a collection
' without prior written permission. This header must remain intact.
Dim rst As DAO.Recordset
Dim DB As DAO.Database
'Dim strSQL As String
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)
If intDayAdd > 0 Then
Do While intDayAdd > 0
datStart = datStart + 1
rst.FindFirst "[HolidayDate] = #" & datStart & "#"
If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday
Then
If rst.NoMatch Then intDayAdd = intDayAdd - 1
End If
Loop
ElseIf intDayAdd < 0 Then
Do While intDayAdd < 0
datStart = datStart - 1
rst.FindFirst "[HolidayDate] = #" & datStart & "#"
If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday
Then
If rst.NoMatch Then intDayAdd = intDayAdd + 1
End If
Loop
End If
GetBusinessDay = datStart
Exit_Here:
rst.Close
Set rst = Nothing
Set DB = Nothing
Exit Function
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Function