Use a table for Holidays in function rather than an array

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

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

Select allOpen in new window

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

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

Select allOpen in new window

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.

<<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!

It sounds like you have it worked out, but post back if you need help.

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:
https://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("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

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