Use a table for Holidays in function rather than an array
I in no way created the attached code. It works - however, rather than using an arry in the code, I am trying to figure out how to use data from the tblHoliday table. That is, I need to have in the code a lookup of hoidays between the varStartDate and varEndDate to use in place of the aryHolidayList. This is in an ACCESS 2003 database in VBA where the user will type in the varStartDate and varEndDate from a form.
Public Function fntWorkDays(varStartDate As Variant, varEndDate As Variant) As Long
'Returns the number of actual workdays between two dates and also accounts for holidays
Dim lngCounter As Long
Dim lngDayCount As Long
Dim aryHolidaysList()
Dim aryMember
Dim blnFound As Boolean
blnFound = False
varStartDate = DateValue(varStartDate)
varEndDate = DateValue(varEndDate)
aryHolidaysList = Array("1/1/2012", "1/16/2012", "2/20/2012", "5/28/2012", "7/4/2012", "9/3/20121", "10/8/2012", _
"11/12/2012", "11/22/2012", "12/25/2012")
For lngCounter = varStartDate To varEndDate
If Weekday(lngCounter, vbMonday) < 6 Then
For Each aryMember In aryHolidaysList
If Format(aryMember, "mm/dd/yyyy") = Format(lngCounter, "mm/dd/yyyy") Then
blnFound = True
Exit For
Else
blnFound = False
End If
Next aryMember
If blnFound = False Then
lngDayCount = lngDayCount + 1
End If
End If
Next lngCounter
fntWorkDays = lngDayCount
End Function
These KB's might help you:
http://support.microsoft.com/kb/210064
http://www.mvps.org/access/datetime/date0006.htm
DatabaseMX, I alerady have the hoilday table created but thank you for the link. The other link returned an error when I clicked on it - could be the bank I am at has a block. I don't think I made myself clear on my question, I need to replace teh lint
aryHolidaysList = Array("1/1/2012", "1/16/2012", "2/20/2012", "5/28/2012", "7/4/2012", "9/3/20121", "10/8/2012", _
"11/12/2012", "11/22/2012", "12/25/2012")
with teh data from teh holiday table. Have been trying to find examples, perhaps a recordset would do the trick? Something like
aryHolidaysList = CurrentDb.OpenRecordset("S
"FROM TblHolidays WHERE (((TblHolidays.HolidayDate
I am not sure it will work, but am going to try.
aryHolidaysList = Array("1/1/2012", "1/16/2012", "2/20/2012", "5/28/2012", "7/4/2012", "9/3/20121", "10/8/2012", _
"11/12/2012", "11/22/2012", "12/25/2012")
with teh data from teh holiday table. Have been trying to find examples, perhaps a recordset would do the trick? Something like
aryHolidaysList = CurrentDb.OpenRecordset("S
"FROM TblHolidays WHERE (((TblHolidays.HolidayDate
I am not sure it will work, but am going to try.
Try this. Make sure you have a reference set for the DAO library, and change the SELECT statement to fit your application:
Public Function fntWorkDays(varStartDate As Variant, varEndDate As Variant) As Long
Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb
'***** Adjust table/Field names as needed
Set rs = db.OpenRecordset("SELECT Holiday AS Hol FROM tblHolidays")
If rs.RecordCount = 0 Then
rs.Close
Set rs = Nothing
MsgBox "No holidays in table"
Exit Function
End If
varStartDate = DateValue(varStartDate)
varEndDate = DateValue(varEndDate)
'aryHolidaysList = Array("1/1/2012", "1/16/2012", "2/20/2012", "5/28/2012", "7/4/2012", "9/3/20121", "10/8/2012", _
'"11/12/2012", "11/22/2012", "12/25/2012")
For lngCounter = varStartDate To varEndDate
If Weekday(lngCounter, vbMonday) < 6 Then
Do Until rs.EOF
If Format(rs!Hol, "mm/dd/yyyy") = Format(lngCounter, "mm/dd/yyyy") Then
blnFound = True
Exit For
Else
blnFound = False
End If
rs.MoveNext
Loop
If blnFound = False Then
lngDayCount = lngDayCount + 1
End If
End If
Next lngCounter
fntWorkDays = lngDayCount
rs.Close
Set rs = Nothing
Set db = Nothing
End Function
Humm ... I just got the the MVPS site no prob. BUT ... it has been up/down recently - a few minutes at a time.
Anyway ... I can maybe look at this later, but seems a recordset approach would be a good start.
mx
Anyway ... I can maybe look at this later, but seems a recordset approach would be a good start.
mx
mbixup, thank you, but I don't want to exit the function if there are not hoildays. I am trying to get the number of workdays between two dates, but also take into account the holidays as listed in my Holiday table. I am trying to replace the aryHolidaysList with a recordset from my holiday table. I have the attached so far, but need to get the For Each to work, even if that is possible.
Public Function fntWorkDays(varStartDate As Variant, varEndDate As Variant) As Long
'Returns the number of actual workdays between two dates and also accounts for holidays
Dim lngCounter As Long
Dim lngDayCount As Long
Dim aryHolidaysList()
Dim rstHolidays As DAO.Recordset
Dim aryMember
Dim blnFound As Boolean
blnFound = False
varStartDate = DateValue(varStartDate)
varEndDate = DateValue(varEndDate)
Set rstHolidays = CurrentDb.OpenRecordset("SELECT HolidayDate FROM TblHolidays " & _
"WHERE HolidayDate Between #" & varStartDate & "# And #" & varEndDate & "# ", dbOpenSnapshot)
For lngCounter = varStartDate To varEndDate
If Weekday(lngCounter, vbMonday) < 6 Then
For Each aryMember In rstHolidays
If Format(aryMember, "mm/dd/yyyy") = Format(lngCounter, "mm/dd/yyyy") Then
blnFound = True
Exit For
Else
blnFound = False
End If
Next aryMember
If blnFound = False Then
lngDayCount = lngDayCount + 1
End If
End If
Next lngCounter
fntWorkDays = lngDayCount
End Function
One more thing, dates are always going to be quarters - actually the quarter start and end dates will be passed in code so the User actually won't type these in and as there are hoildays in every quarter, the recordset should be populated. And I just had a thought, since I only need a count, perhaps a better approach would be could the workdays, then substact the recordcound from the recordset? It is still mathmatically correct.
Okay try this revision... it doesnt exit if the table is empty; it just skips the recordset code. See if that results in the right number of days...:
Public Function fntWorkDays(varStartDate As Variant, varEndDate As Variant) As Long
Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb
'***** Adjust table/Field names as needed
Set rs = db.OpenRecordset("SELECT Holiday AS Hol FROM tblHolidays")
varStartDate = DateValue(varStartDate)
varEndDate = DateValue(varEndDate)
'aryHolidaysList = Array("1/1/2012", "1/16/2012", "2/20/2012", "5/28/2012", "7/4/2012", "9/3/20121", "10/8/2012", _
'"11/12/2012", "11/22/2012", "12/25/2012")
For lngCounter = varStartDate To varEndDate
If Weekday(lngCounter, vbMonday) < 6 Then
If rs.RecordCount > 0 Then
Do Until rs.EOF
If Format(rs!Hol, "mm/dd/yyyy") = Format(lngCounter, "mm/dd/yyyy") Then
blnFound = True
Exit Do
Else
blnFound = False
End If
rs.MoveNext
Loop
End If
If blnFound = False Then
lngDayCount = lngDayCount + 1
End If
End If
Next lngCounter
fntWorkDays = lngDayCount
rs.Close
Set rs = Nothing
Set db = Nothing
End Function
<<And I just had a thought, since I only need a count, perhaps a better approach would be could the workdays, then substact the recordcound from the recordset?>>
That is a great approach!
That is a great approach!
Btw, you could use a DCount to get that count instead of a recordset:
NumHolidaysInRange = DCount("*","TblHolidays", "HolidayDate Between #" & varStartDate & "# And #" & varEndDate & "#")
See Jim Dettman's article on DLookup and the domain functions:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_12-Dlookup-and-the-Domain-Functions.html
NumHolidaysInRange = DCount("*","TblHolidays", "HolidayDate Between #" & varStartDate & "# And #" & varEndDate & "#")
See Jim Dettman's article on DLookup and the domain functions:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_12-Dlookup-and-the-Domain-Functions.html
Ok, this is the final and works. I like the DCOUNT and my experiment with that and thanks for the reference suggestions.
Public Function fntWorkDays(varStartDate As Variant, varEndDate As Variant) As Long
Dim lngCounter As Long
Dim lngDayCount As Long
Dim intHolidays As Integer
Dim blnFound As Boolean
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("S
"WHERE HolidayDate Between #" & varStartDate & "# And #" & varEndDate & "# ", dbOpenSnapshot)
rs.MoveLast
intHolidays = rs.RecordCount
varStartDate = DateValue(varStartDate)
varEndDate = DateValue(varEndDate)
For lngCounter = varStartDate To varEndDate
If Weekday(lngCounter, vbMonday) < 6 Then
If rs.RecordCount > 0 Then
Do Until rs.EOF
If Format(rs!Hol, "mm/dd/yyyy") = Format(lngCounter, "mm/dd/yyyy") Then
blnFound = True
Exit Do
Else
blnFound = False
End If
rs.MoveNext
Loop
End If
If blnFound = False Then
lngDayCount = lngDayCount + 1
End If
End If
Next lngCounter
fntWorkDays = lngDayCount - intHolidays
rs.Close
Set rs = Nothing
End Function
Public Function fntWorkDays(varStartDate As Variant, varEndDate As Variant) As Long
Dim lngCounter As Long
Dim lngDayCount As Long
Dim intHolidays As Integer
Dim blnFound As Boolean
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("S
"WHERE HolidayDate Between #" & varStartDate & "# And #" & varEndDate & "# ", dbOpenSnapshot)
rs.MoveLast
intHolidays = rs.RecordCount
varStartDate = DateValue(varStartDate)
varEndDate = DateValue(varEndDate)
For lngCounter = varStartDate To varEndDate
If Weekday(lngCounter, vbMonday) < 6 Then
If rs.RecordCount > 0 Then
Do Until rs.EOF
If Format(rs!Hol, "mm/dd/yyyy") = Format(lngCounter, "mm/dd/yyyy") Then
blnFound = True
Exit Do
Else
blnFound = False
End If
rs.MoveNext
Loop
End If
If blnFound = False Then
lngDayCount = lngDayCount + 1
End If
End If
Next lngCounter
fntWorkDays = lngDayCount - intHolidays
rs.Close
Set rs = Nothing
End Function
FYI: This is the code that also works using the DCOUNT
Public Function fntWorkDays(varStartDate As Variant, varEndDate As Variant) As Long
Dim lngCounter As Long
Dim lngDayCount As Long
Dim intHolidays As Integer
Dim blnFound As Boolean
intHolidays = DCount("*", "TblHolidays", "HolidayDate Between #" & varStartDate & "# And #" & varEndDate & "#")
varStartDate = DateValue(varStartDate)
varEndDate = DateValue(varEndDate)
For lngCounter = varStartDate To varEndDate
If Weekday(lngCounter, vbMonday) < 6 Then
If blnFound = False Then
lngDayCount = lngDayCount + 1
End If
End If
Next lngCounter
fntWorkDays = lngDayCount - intHolidays
End Function
Public Function fntWorkDays(varStartDate As Variant, varEndDate As Variant) As Long
Dim lngCounter As Long
Dim lngDayCount As Long
Dim intHolidays As Integer
Dim blnFound As Boolean
intHolidays = DCount("*", "TblHolidays", "HolidayDate Between #" & varStartDate & "# And #" & varEndDate & "#")
varStartDate = DateValue(varStartDate)
varEndDate = DateValue(varEndDate)
For lngCounter = varStartDate To varEndDate
If Weekday(lngCounter, vbMonday) < 6 Then
If blnFound = False Then
lngDayCount = lngDayCount + 1
End If
End If
Next lngCounter
fntWorkDays = lngDayCount - intHolidays
End Function
Premium Content
You need an Expert Office subscription to comment.Start Free Trial