We help IT Professionals succeed at work.

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

Open in new window

Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Sandra SmithRetired

Author

Commented:
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("SELECT TblHolidays.HolidayDate " & _
    "FROM TblHolidays WHERE (((TblHolidays.HolidayDate) Between #" & varStartDate & "# And #" & varEndDate & "#))")

I am not sure it will work, but am going to try.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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

Open in new window

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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
Sandra SmithRetired

Author

Commented:
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

Open in new window

Sandra SmithRetired

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
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

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
<<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!
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
It sounds like you have it worked out, but post back if you need help.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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
Sandra SmithRetired

Author

Commented:
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("SELECT HolidayDate As Hol FROM TblHolidays " & _
         "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
Sandra SmithRetired

Author

Commented:
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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.