Previous Business Day

[Jen]

The following code is supposed to determine the previous business day, but
while testing BgnDate = 12/29/09, it is returning 12/29/09 instead of
12/28/09 as intended.
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,

Please see my answer to your previous post.

Clifford Bass