RE: Help with VB Script
- From: kontra <kontra@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 17 Mar 2006 12:24:27 -0800
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" wrote:
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.
- Follow-Ups:
- Re: Help with VB Script
- From: SteveS
- Re: Help with VB Script
- Prev by Date: RE: Function name not found
- Next by Date: Re: dim as database
- Previous by thread: RE: Function name not found
- Next by thread: Re: Help with VB Script
- Index(es):
Relevant Pages
|
