M
Mark
I'm having trouble understanding a problem with date calculations that
behaves differently if I call the routine from a form, or run as a test in
code.
What I am trying to do is display projected completion date for a
manufacturing operation. I have the operation start date and cycle days for
the operation, and want to add the two to predict the completion date in work
days. Since Access does not have a workday function, I borrowed one from
Arvin Myer (thank you Arvin). I have a text box on a form (frmCurrentStatus)
which calls the "GetBusinessDay" function and should return the next work day
the operation will finish, skipping holidays and weekend days.
The frmCurrentStatus form is based on a query that provides the OpStart and
Cycle day values. I using a switchboard form which calls a macro which opens
the status form.
The values I pass are OpStart = 5/19/09, and cycle days = 4, return date
should be 5/26/09 since Monday 5/25/09 is holiday in holiday table.
The frmGetBusinessDay form incorrectly returns the date 5/25/09.
If I call the GetBusinessDay function from a test routine in the VB module,
the GetBusiinessDay function returns 5/26/09, which is correct. It appears
that the GetBusinessDay function is okay, but for some reason the text box on
frmGetBusinessDay is wrong.
The text box has the Control Source set to
=GetBusinessDay([OpStart],[CycleDays])
I have been using Access 2007 at home and Access 2002 at work to develop
this, I have not yet tested the most current version on Access 2002 at work
yet. Will try that next week.
Is it possible that that there is a problem with references in VB?
It seems like the problem should be obvious, but I don't see it yet... Any
suggestions would be appreciated.
Mark V
See code of test procedure and GetBusinessDay function below.
'--------------------------------------------------------------------------
'--- This is test procedure to call GetBusinessDay
Private Sub pXX()
Dim datStart As Date
Dim intDayAdd As Integer
Dim datNew As Date
Dim datOld As Date
MsgBox "Start", vbOKOnly
datStart = #5/19/2009#
intDayAdd = 4
datOld = datStart
datNew = GetBusinessDay(datStart, intDayAdd)
MsgBox "After Return - datNew = " & datNew & " " & datOld & " " &
intDayAdd & " " & datStart, vbOKOnly
End Sub
'-------------------------------------------------------------------------------
Public Function GetBusinessDay(datStart As Date, intDayAdd As Integer) As Date
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.
'-- Dimension objects
Dim rst As DAO.Recordset
Dim db As DAO.Database
'--- Set db object and create recordset from Holiday table
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)
'--- debug message
MsgBox "recieved = " & datStart & " " & intDayAdd, vbOKOnly
'--- Look forward
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
'--- Look Backward
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
'-- Set value for function to return
GetBusinessDay = datStart
'-- debug message
MsgBox "getbusinessday before return = " & GetBusinessDay
'--- Exit and error messages
'--- close objects
Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Function
behaves differently if I call the routine from a form, or run as a test in
code.
What I am trying to do is display projected completion date for a
manufacturing operation. I have the operation start date and cycle days for
the operation, and want to add the two to predict the completion date in work
days. Since Access does not have a workday function, I borrowed one from
Arvin Myer (thank you Arvin). I have a text box on a form (frmCurrentStatus)
which calls the "GetBusinessDay" function and should return the next work day
the operation will finish, skipping holidays and weekend days.
The frmCurrentStatus form is based on a query that provides the OpStart and
Cycle day values. I using a switchboard form which calls a macro which opens
the status form.
The values I pass are OpStart = 5/19/09, and cycle days = 4, return date
should be 5/26/09 since Monday 5/25/09 is holiday in holiday table.
The frmGetBusinessDay form incorrectly returns the date 5/25/09.
If I call the GetBusinessDay function from a test routine in the VB module,
the GetBusiinessDay function returns 5/26/09, which is correct. It appears
that the GetBusinessDay function is okay, but for some reason the text box on
frmGetBusinessDay is wrong.
The text box has the Control Source set to
=GetBusinessDay([OpStart],[CycleDays])
I have been using Access 2007 at home and Access 2002 at work to develop
this, I have not yet tested the most current version on Access 2002 at work
yet. Will try that next week.
Is it possible that that there is a problem with references in VB?
It seems like the problem should be obvious, but I don't see it yet... Any
suggestions would be appreciated.
Mark V
See code of test procedure and GetBusinessDay function below.
'--------------------------------------------------------------------------
'--- This is test procedure to call GetBusinessDay
Private Sub pXX()
Dim datStart As Date
Dim intDayAdd As Integer
Dim datNew As Date
Dim datOld As Date
MsgBox "Start", vbOKOnly
datStart = #5/19/2009#
intDayAdd = 4
datOld = datStart
datNew = GetBusinessDay(datStart, intDayAdd)
MsgBox "After Return - datNew = " & datNew & " " & datOld & " " &
intDayAdd & " " & datStart, vbOKOnly
End Sub
'-------------------------------------------------------------------------------
Public Function GetBusinessDay(datStart As Date, intDayAdd As Integer) As Date
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.
'-- Dimension objects
Dim rst As DAO.Recordset
Dim db As DAO.Database
'--- Set db object and create recordset from Holiday table
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)
'--- debug message
MsgBox "recieved = " & datStart & " " & intDayAdd, vbOKOnly
'--- Look forward
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
'--- Look Backward
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
'-- Set value for function to return
GetBusinessDay = datStart
'-- debug message
MsgBox "getbusinessday before return = " & GetBusinessDay
'--- Exit and error messages
'--- close objects
Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Function