Help with VB Script

K

kontra

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

*********
 
S

SteveS

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
 
K

kontra

Thank you Steve of course your code looks more like from someone who have
done it before :)

this is how i got it to work and i tested it and it works, I have changed
the system date to test it ;)
'******************************************************

Public Function PreviousBD() As Date

Dim rsHolidays ' Should be coming from tbl_Holidays in current DB
Dim bdNum ' Assign DayOfTheWeek Value
Dim fldHdate As Integer ' will use to store value 1 for holiday and 2 for
non-holiday
Dim Yesterday As Date


Yesterday = Date - 1
bdNum = Weekday(Date)

' if today is saturday or sunday then I do not want to run anything at all

If bdNum = 7 Then
DoCmd.Quit
Else:
If bdNum = 1 Then
DoCmd.Quit
End If
End If

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HDate] FROM tbl_Holidays
Where HDate=Date()-1", dbOpenSnapshot)
'Looking if yesterday was a holiday


If rsHolidays.EOF Then ' EOF stands for Empty recordset
fldHdate = 1 ' Assigned value if Yesterday was not a holiday
Else: fldHdate = 2
End If

If bdNum = 2 Then
PreviousBD = Yesterday - 2
Else:
If bdNum = 3 And fldHdate = 2 Then
PreviousBD = Yesterday - 3
Else:
If bdNum = 4 And fldHdate = 2 Or bdNum = 5 And fldHdate = 2 Or
bdNum = 6 And fldHdate = 2 Then
PreviousBD = Yesterday - 1
Else:
If bdNum = 3 Or bdNum = 4 Or bdNum = 5 Or bdNum = 6 Then
PreviousBD = Yesterday
End If
End If
End If
End If

' to view results in Immediate window

Debug.Print Yesterday
Debug.Print bdNum
Debug.Print fldHdate
Debug.Print PreviousBD

Set rsHolidays = Nothing

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


SteveS said:
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 said:
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

*********
Click to expand...
Click to expand...
 
S

SteveS

kontra,

It is good to hear that you got it working.


A added some comments in your code :)

FYI, the colon ( : ) is not needed after ELSE

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

Dim rsHolidays ' Should be coming from tbl_Holidays in current DB
Dim bdNum ' Assign DayOfTheWeek Value
'###########
'
' if the type is unspecified, the type is Variant, so set type
'
' Dim rsHolidays As Recordset
' Dim bdNum As Integer ' Assign DayOfTheWeek Value
'
'###########
Dim fldHdate As Integer ' will use to store value 1 for holiday and 2 for
non-holiday
Dim Yesterday As Date


Yesterday = Date - 1
bdNum = Weekday(Date)

'#################################

'****** do you really want to QUIT?

' if today is Saturday or sunday then I do not want to run anything at all
If bdNum = 7 Then
DoCmd.Quit
Else
If bdNum = 1 Then
DoCmd.Quit
End If
End If

'**** this is the same as above
' If bdNum = 7 Or bdNum = 1 Then
' DoCmd.Quit
' End If
'#################################

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HDate] FROM tbl_Holidays
Where HDate=Date()-1", dbOpenSnapshot)
'Looking if yesterday was a holiday

If rsHolidays.EOF Then ' EOF stands for Empty recordset
'###########
'
'EOF really means End Of File; BOF is Beginning Of File
' If you want to check for an empty recordset use
'
'If rsHolidays.BOF And rsHolidays.EOF Then
'
'###########

fldHdate = 1 ' Assigned value if Yesterday was not a holiday
Else
fldHdate = 2
End If


If bdNum = 2 Then
PreviousBD = Yesterday - 2
Else
If bdNum = 3 And fldHdate = 2 Then
PreviousBD = Yesterday - 3
Else
'###########
'
' You should use parenthesis to set the order Access evaluates this line
' this is the way it is being evaluated now
'
'If (bdNum = 4 And fldHdate = 2) Or (bdNum = 5 And fldHdate = 2) Or
(bdNum = 6 And fldHdate = 2) Then
'
'###########
If bdNum = 4 And fldHdate = 2 Or bdNum = 5 And fldHdate = 2 Or bdNum
= 6 And fldHdate = 2 Then
PreviousBD = Yesterday - 1
Else
If bdNum = 3 Or bdNum = 4 Or bdNum = 5 Or bdNum = 6 Then
PreviousBD = Yesterday
End If
End If
End If
End If

' to view results in Immediate window

Debug.Print Yesterday
Debug.Print bdNum
Debug.Print fldHdate
Debug.Print PreviousBD


'###########
rsHolidays.Close
'###########


Set rsHolidays = Nothing

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


HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)
Thank you Steve of course your code looks more like from someone who have
done it before :)

this is how i got it to work and i tested it and it works, I have changed
the system date to test it ;)
'******************************************************

Public Function PreviousBD() As Date

Dim rsHolidays ' Should be coming from tbl_Holidays in current DB
Dim bdNum ' Assign DayOfTheWeek Value
Dim fldHdate As Integer ' will use to store value 1 for holiday and 2 for
non-holiday
Dim Yesterday As Date


Yesterday = Date - 1
bdNum = Weekday(Date)

' if today is saturday or sunday then I do not want to run anything at all

If bdNum = 7 Then
DoCmd.Quit
Else:
If bdNum = 1 Then
DoCmd.Quit
End If
End If

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HDate] FROM tbl_Holidays
Where HDate=Date()-1", dbOpenSnapshot)
'Looking if yesterday was a holiday


If rsHolidays.EOF Then ' EOF stands for Empty recordset
fldHdate = 1 ' Assigned value if Yesterday was not a holiday
Else: fldHdate = 2
End If

If bdNum = 2 Then
PreviousBD = Yesterday - 2
Else:
If bdNum = 3 And fldHdate = 2 Then
PreviousBD = Yesterday - 3
Else:
If bdNum = 4 And fldHdate = 2 Or bdNum = 5 And fldHdate = 2 Or
bdNum = 6 And fldHdate = 2 Then
PreviousBD = Yesterday - 1
Else:
If bdNum = 3 Or bdNum = 4 Or bdNum = 5 Or bdNum = 6 Then
PreviousBD = Yesterday
End If
End If
End If
End If

' to view results in Immediate window

Debug.Print Yesterday
Debug.Print bdNum
Debug.Print fldHdate
Debug.Print PreviousBD

Set rsHolidays = Nothing

End Function
*********************
Click to expand...
 
K

kontra

Thank you SteveS for you help!

I do want to quit (I have it scheduled via MS task scheduler and as far as I
know that doesn't recognize weekend. I do not want the report that uses that
function to be email on the weekend) however past weekend it still went
through, even it suppose to quit, else it worked fine.
--
If at first you don''''t succeed, destroy all evidence that you tried.


SteveS said:
kontra,

It is good to hear that you got it working.


A added some comments in your code :)

FYI, the colon ( : ) is not needed after ELSE

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

Dim rsHolidays ' Should be coming from tbl_Holidays in current DB
Dim bdNum ' Assign DayOfTheWeek Value
'###########
'
' if the type is unspecified, the type is Variant, so set type
'
' Dim rsHolidays As Recordset
' Dim bdNum As Integer ' Assign DayOfTheWeek Value
'
'###########
Dim fldHdate As Integer ' will use to store value 1 for holiday and 2 for
non-holiday
Dim Yesterday As Date


Yesterday = Date - 1
bdNum = Weekday(Date)

'#################################

'****** do you really want to QUIT?

' if today is Saturday or sunday then I do not want to run anything at all
If bdNum = 7 Then
DoCmd.Quit
Else
If bdNum = 1 Then
DoCmd.Quit
End If
End If

'**** this is the same as above
' If bdNum = 7 Or bdNum = 1 Then
' DoCmd.Quit
' End If
'#################################

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HDate] FROM tbl_Holidays
Where HDate=Date()-1", dbOpenSnapshot)
'Looking if yesterday was a holiday

If rsHolidays.EOF Then ' EOF stands for Empty recordset
'###########
'
'EOF really means End Of File; BOF is Beginning Of File
' If you want to check for an empty recordset use
'
'If rsHolidays.BOF And rsHolidays.EOF Then
'
'###########

fldHdate = 1 ' Assigned value if Yesterday was not a holiday
Else
fldHdate = 2
End If


If bdNum = 2 Then
PreviousBD = Yesterday - 2
Else
If bdNum = 3 And fldHdate = 2 Then
PreviousBD = Yesterday - 3
Else
'###########
'
' You should use parenthesis to set the order Access evaluates this line
' this is the way it is being evaluated now
'
'If (bdNum = 4 And fldHdate = 2) Or (bdNum = 5 And fldHdate = 2) Or
(bdNum = 6 And fldHdate = 2) Then
'
'###########
If bdNum = 4 And fldHdate = 2 Or bdNum = 5 And fldHdate = 2 Or bdNum
= 6 And fldHdate = 2 Then
PreviousBD = Yesterday - 1
Else
If bdNum = 3 Or bdNum = 4 Or bdNum = 5 Or bdNum = 6 Then
PreviousBD = Yesterday
End If
End If
End If
End If

' to view results in Immediate window

Debug.Print Yesterday
Debug.Print bdNum
Debug.Print fldHdate
Debug.Print PreviousBD


'###########
rsHolidays.Close
'###########


Set rsHolidays = Nothing

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


HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)
Thank you Steve of course your code looks more like from someone who have
done it before :)

this is how i got it to work and i tested it and it works, I have changed
the system date to test it ;)
'******************************************************

Public Function PreviousBD() As Date

Dim rsHolidays ' Should be coming from tbl_Holidays in current DB
Dim bdNum ' Assign DayOfTheWeek Value
Dim fldHdate As Integer ' will use to store value 1 for holiday and 2 for
non-holiday
Dim Yesterday As Date


Yesterday = Date - 1
bdNum = Weekday(Date)

' if today is saturday or sunday then I do not want to run anything at all

If bdNum = 7 Then
DoCmd.Quit
Else:
If bdNum = 1 Then
DoCmd.Quit
End If
End If

Set rsHolidays = CurrentDb.OpenRecordset("SELECT [HDate] FROM tbl_Holidays
Where HDate=Date()-1", dbOpenSnapshot)
'Looking if yesterday was a holiday


If rsHolidays.EOF Then ' EOF stands for Empty recordset
fldHdate = 1 ' Assigned value if Yesterday was not a holiday
Else: fldHdate = 2
End If

If bdNum = 2 Then
PreviousBD = Yesterday - 2
Else:
If bdNum = 3 And fldHdate = 2 Then
PreviousBD = Yesterday - 3
Else:
If bdNum = 4 And fldHdate = 2 Or bdNum = 5 And fldHdate = 2 Or
bdNum = 6 And fldHdate = 2 Then
PreviousBD = Yesterday - 1
Else:
If bdNum = 3 Or bdNum = 4 Or bdNum = 5 Or bdNum = 6 Then
PreviousBD = Yesterday
End If
End If
End If
End If

' to view results in Immediate window

Debug.Print Yesterday
Debug.Print bdNum
Debug.Print fldHdate
Debug.Print PreviousBD

Set rsHolidays = Nothing

End Function
*********************
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Previous Business Day 3
Previous Business Day 1
How to go through each recordset field value? 3
Help with function 4
Countdown macro - display settings 2
How to use/create code in MS Word to calculate age? 0
VBA - exporting serial letters to single documents 0
VBA Coding Help for Beginner 0

Top