RE: Help with VB Script

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

First,

Date is a reserved Access/VBA/Jet word and should not be used as a field
name.
For additional reserved words, see the Microsoft KnowledgeBase article for
your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

The only If statement that would run is the first one. If it was FALSE, none
of the other IF()'s would be evaluated. You would need to have separate IF()s

If bdNum = 1 And Date <> rsHolidays Then
PreviousBD = Date - 3
End If

If bdNum > 1 And Date - 1 = rsHolidays Then
PreviousBD = Date - 2
End If
..
..
..

or use IF..ELSEIF...END IF :

If bdNum = 1 And Date <> rsHolidays Then
PreviousBD = TheDate - 3

ElseIf bdNum > 1 And Date - 1 = rsHolidays Then
PreviousBD = TheDate - 2

ElseIf bdNum > 1 And Date - 1 <> rsHolidays Then
PreviousBD = TheDate - 1

ElseIf bdNum = 2 And Date - 1 = rsHolidays Then
PreviousBD = TheDate - 4

ElseIf bdNum = 1 And Date - 3 = rsHolidays Then
PreviousBD = TheDate - 4

End If


So I rewrote your code... :)

The usual disclaimers apply - AIR CODE!! (means untested) -Watch for line
wrap - try this on a copy of your database....

'***beg code **********
Public Function PreviousBD(TheDate As Date) As Date

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

strSQL = "Select HolidayDate from tbl_Holidays"
Set rsHolidays = CurrentDb.OpenRecordset(strSQL)

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

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

'clean up
rsHolidays.Close
Set rsHolidays = Nothing

'return Previous Business Day
PreviousBD = TheDate

End Function
'***end code **********

Pass the function a date and it should return the previous BD that is not a
holiday.

If you have questions, post back...

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"kontra" wrote:

Hi,

I am very very new to VB script. What I am trying to achieve is to get
Previous Business Day as Date to use in a query...

Below is the 'code' I came up with after looking on the net and this forum.
However I am still not sure what parameters to use when openning the
recordset, plus I feel that I am missing some sort of a loop function to
check for all the holidays listed in the table. Also if anyone has any
suggestions on how to correct my code I would really appreciate if you will
explain as to why, so I can understand it better and not ask the same
question again. Thank you:
**********
Option Compare Database

Option Explicit

Public Function PreviousBD() As Date

Dim dbConn As ADODB.Connection ' Not sure if needed
Dim rsHolidays ' Should be coming from tbl_Holidays in current DB
Dim bdNum

bdNum = Weekday(Date)

Set rsHolidays = CurrentDb.OpenRecordset("Select Date from tbl_Holidays")
'Not sure what parameters to use

If bdNum = 1 And Date <> rsHolidays Then
PreviousBD = Date - 3

If bdNum > 1 And Date - 1 = rsHolidays Then
PreviousBD = Date - 2

If bdNum > 1 And Date - 1 <> rsHolidays Then
PreviousBD = Date - 1

If bdNum = 2 And Date - 1 = rsHolidays Then
PreviousBD = Date - 4

If bdNum = 1 And Date - 3 = rsHolidays Then
PreviousBD = Date - 4
End If
End If
End If
End If
End If

Set rsHolidays = Nothing

End Function

*********
--
If at first you don''''t succeed, destroy all evidence that you tried.
.