Previous Business Day

[Jen]

I found the following code for determing Previous Business Day on this site.
When I test is using "12/29/09" as the BgnDate, it returns "12/29/09" when it
should be returning "12/28/09".

Any thoughts?

Public Function PreviousBD(BgnDate As Date) As Date

Dim rsHolidays As DAO.Recordset
Dim bdNum As Integer
Dim isHoliday As Boolean
Dim strSQL As String

strSQL = "Select Holiday from tblBCBSMN_Holidays"
Set rsHolidays = CurrentDb.OpenRecordset(strSQL)

Do
bdNum = Weekday(BgnDate)
'determine which day the date is,
'then calc previous BD
Select Case bdNum
'Tuesday to Saturday
Case 3 - 7
BgnDate = BgnDate - 1
'Sunday
Case 1
BgnDate = BgnDate - 2
'Monday
Case 2
BgnDate = BgnDate - 3
End Select

' now check if BgnDate is a holiday
'search the recordset
rsHolidays.FindFirst "[Holiday] = #" & BgnDate & "#"
If rsHolidays.NoMatch Then
Exit Do
Else
BgnDate = BgnDate - 1
'check once more for back to back holidays
rsHolidays.FindFirst "[Holiday] = #" & BgnDate & "#"
If rsHolidays.NoMatch Then 'True = no records
Exit Do
Else
BgnDate = BgnDate - 1
End If
End If
Loop

'clean up
rsHolidays.Close
Set rsHolidays = Nothing

'return Previous Business Day
PreviousBD = BgnDate

End Function

Thanks!

 

[Clifford Bass via AccessMonster.com]

Hi Jen,

I would expect it to provide bad results for a lot of dates due to the
"Case 3 - 7" statement. Note that that does not mean do this when the value
is between 3 and 7, but only do it when the value is 3 - 7, which equals -4.
The statement should really be "Case 3 To 7". Regardless, it seems to be a
rather complicated way to get the prior business day and it will not always
give you valid results even with the Case statement fixed. If you are pass
it the Tuesday after a Monday holiday, say 1/19/2010 which is just after MLK
Jr. Day, you get the Sunday date.

? PreviousBD(#1/19/2010#)
1/17/2010

Here is a better function that will work in more situations than that
one and that will deal with null dates in case your date columns can be null
or blank or zero-length strings and it will deal with dates stored as text.
It returns "#Error..." when there is some problem instead of just bombing out.


Public Function PreviousBusinessDay(varDate As Variant) As Variant

Dim rsHolidays As DAO.Recordset

On Error GoTo Handle_Error

If IsNull(varDate) Then
PreviousBusinessDay = Null
Else
If IsDate(varDate) Then
varDate = CDate(varDate)
Set rsHolidays = CurrentDb.OpenRecordset( _
"select HolidayDate " & _
"from tblHolidays", _
DAO.RecordsetTypeEnum.dbOpenSnapshot)
With rsHolidays
Do While True
varDate = varDate - 1
If Weekday(varDate, vbMonday) <= 5 Then
.FindFirst "[HolidayDate] = #" & Format(varDate,
"yyyy-mm-dd") & "#"
If .NoMatch Then
Exit Do
End If
End If
Loop
End With
PreviousBusinessDay = varDate
Else
If Trim(varDate) = vbNullString Then
PreviousBusinessDay = Null
Else
PreviousBusinessDay = "#Error (Not a date.)"
End If
End If
End If

Exit_Function:
On Error Resume Next

If Not rsHolidays Is Nothing Then
rsHolidays.Close
Set rsHolidays = Nothing
End If
Exit Function

Handle_Error:
PreviousBusinessDay = "#Error (" & err.Description & ")"
Resume Exit_Function

End Function

? PreviousBusinessDay(#1/19/2010#)
1/15/2010

Hope that helps,

Clifford Bass

I found the following code for determing Previous Business Day on this site.
When I test is using "12/29/09" as the BgnDate, it returns "12/29/09" when it
should be returning "12/28/09".

Any thoughts?

Public Function PreviousBD(BgnDate As Date) As Date

Dim rsHolidays As DAO.Recordset
Dim bdNum As Integer
Dim isHoliday As Boolean
Dim strSQL As String

strSQL = "Select Holiday from tblBCBSMN_Holidays"
Set rsHolidays = CurrentDb.OpenRecordset(strSQL)

Do
bdNum = Weekday(BgnDate)
'determine which day the date is,
'then calc previous BD
Select Case bdNum
'Tuesday to Saturday
Case 3 - 7
BgnDate = BgnDate - 1
'Sunday
Case 1
BgnDate = BgnDate - 2
'Monday
Case 2
BgnDate = BgnDate - 3
End Select

' now check if BgnDate is a holiday
'search the recordset
rsHolidays.FindFirst "[Holiday] = #" & BgnDate & "#"
If rsHolidays.NoMatch Then
Exit Do
Else
BgnDate = BgnDate - 1
'check once more for back to back holidays
rsHolidays.FindFirst "[Holiday] = #" & BgnDate & "#"
If rsHolidays.NoMatch Then 'True = no records
Exit Do
Else
BgnDate = BgnDate - 1
End If
End If
Loop

'clean up
rsHolidays.Close
Set rsHolidays = Nothing

'return Previous Business Day
PreviousBD = BgnDate

End Function

Thanks!

 

[a0a349]

Thanks Clifford!
Sorry for the duplicate posts. I was attempting to post on the Microsoft
Access Discussions boards but it wouldn't show up.???Weird, then upon a
google search came upon my own posting here.

Anyway, thank you...it works great!

Jen

Hi Jen,

I would expect it to provide bad results for a lot of dates due to the
"Case 3 - 7" statement. Note that that does not mean do this when the value
is between 3 and 7, but only do it when the value is 3 - 7, which equals -4.
The statement should really be "Case 3 To 7". Regardless, it seems to be a
rather complicated way to get the prior business day and it will not always
give you valid results even with the Case statement fixed. If you are pass
it the Tuesday after a Monday holiday, say 1/19/2010 which is just after MLK
Jr. Day, you get the Sunday date.

? PreviousBD(#1/19/2010#)
1/17/2010

Here is a better function that will work in more situations than that
one and that will deal with null dates in case your date columns can be null
or blank or zero-length strings and it will deal with dates stored as text.
It returns "#Error..." when there is some problem instead of just bombing out.

Public Function PreviousBusinessDay(varDate As Variant) As Variant

Dim rsHolidays As DAO.Recordset

On Error GoTo Handle_Error

If IsNull(varDate) Then
PreviousBusinessDay = Null
Else
If IsDate(varDate) Then
varDate = CDate(varDate)
Set rsHolidays = CurrentDb.OpenRecordset( _
"select HolidayDate " & _
"from tblHolidays", _
DAO.RecordsetTypeEnum.dbOpenSnapshot)
With rsHolidays
Do While True
varDate = varDate - 1
If Weekday(varDate, vbMonday) <= 5 Then
.FindFirst "[HolidayDate] = #" & Format(varDate,
"yyyy-mm-dd") & "#"
If .NoMatch Then
Exit Do
End If
End If
Loop
End With
PreviousBusinessDay = varDate
Else
If Trim(varDate) = vbNullString Then
PreviousBusinessDay = Null
Else
PreviousBusinessDay = "#Error (Not a date.)"
End If
End If
End If

Exit_Function:
On Error Resume Next

If Not rsHolidays Is Nothing Then
rsHolidays.Close
Set rsHolidays = Nothing
End If
Exit Function

Handle_Error:
PreviousBusinessDay = "#Error (" & err.Description & ")"
Resume Exit_Function

End Function

? PreviousBusinessDay(#1/19/2010#)
1/15/2010

Hope that helps,

Clifford Bass

I found the following code for determing Previous Business Day on this site.
When I test is using "12/29/09" as the BgnDate, it returns "12/29/09" when it

[quoted text clipped - 55 lines]

Thanks!

 

[Clifford Bass via AccessMonster.com]

Hi Jen,

No problem. I mostly posted the stuff in the duplicate postings so that
others would know that there were multiple postings. The MS Access
Discussion boards have not been working well lately. The message reply
notifications have been non-functional. Posts can take a while to show up.
Also, they have a filter on them that can prevent posts from showing if there
are certain things in them. For instance I once replied to someone who
inadvertently left off the f from the word "shift" in his question and MS's
boards would not show my reply because of that. But the reply was out there
somewhere. And it showed the original post! Go figure!

Anyway, you are welcome and glad to have been of help.

Clifford Bass