• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 506
  • Last Modified:

create an array from a table in MS Access

Dear Experts,
I need your help. I am trying to create an array by using a table called "Holidays" and then I will use this array with an excel function below.

Workday = xlApp.Workday(startDate, numberOfDays, HolidaysA)

Table Name: Holidays

The probelm is I dont know how to do as I have a little knowledge in VBA. I have found some code on the internet and I tried to use it, but it is not working:
    Dim rs As ADODB.Recordset
    Dim Holidays As Variant
    Set rs = CurrentProject.Connection.Execute("select * from Holidays")
    HolidaysA = rs.GetRows()

Thank you very much in adavce for your help.
  • 2
1 Solution
Can we be totally clear about...

Where is the table?

Where are you running the code?

white_stoneAuthor Commented:
Dear peter57r,

Thank you for your quick reponse. I have created the table in Micorsoft Access 2007 and I run this code on module VBA. Some of the code in found on msdn website and another. Please find attached the file.

Thank you.
Patrick MatthewsCommented:
Automating Excel merely to get the use of the Workday function strikes me as a bit much, especially in light of Workday's limitations:

1) Workday will not allow you to specify the regular working days; you must use Mon-Fri
2) If you enter 0 as the offset for Workday it will always return the start date, even if the start date is itself a non working day

This function does everything Workday can do, and in addition to that:

1) Can tap directly into a "holidays table" in your database
2) Allows you to specify which days of the week are working days
3) Allows you to override the default behavior for zero offset

The function is here:

Public Function WorkdayXHolidayTbl(StartDate As Date, DayOffset As Long, Optional UseDays As String = "23456", _
    Optional ZeroMode As Long = 1, Optional HolidayTbl As String = "", Optional HolidayCol As String = "") As Date
    ' This function requires a reference to the DAO library
    ' Function returns a date offset from the StartDate argument the number of days determined by
    ' the DayOffset argument, counting only "work days" specified by the UseDays argument.  Function
    ' is designed for Access, as it assumes that holidays are stored in a holiday table
    ' Function differs from the Excel function WORKDAY in that WORKDAY always counts Monday-Friday,
    ' while WorkdayXHolidayTbl allows the user to specify the days that should be counted.  Also, with
    ' WORKDAY, if the offset is zero, the function always returns the start date as the answer, even if
    ' the start date is on a weekend.  WorkdayXHolidayTbl allows the user to specify what should happen
    ' if the offset is zero and the start date is on a non-working day.
    ' Arguments:
    ' 1) StartDate is the original starting point, from which you want a working day offset
    ' 2) DayOffset is the number of whole days away from the starting point, counting only "working days".
    '    A zero or negative offset is allowed
    ' 3) UseDays is a string encoding what weekdays count as working days.  Use 1=Sun, 2=Mon, ..., 7=Sat.
    '    Thus, "23456" indicates Mon-Fri should be counted, and "147" means only Sun, Wed, and Sat count.
    '    Use holidays to override this working day setting for particular dates
    ' 4) ZeroMode controls how the function behaves when the offset is zero and the start date is a non
    '    working day.  Use 0 when you want the function to return the start date (as Excel's WORKDAY
    '    function would), 1 to pick the next working day, or -1 to pick the previous working day
    ' 5) HolidayTbl is an optional argument that is the name of the table that stores holidays, which
    '    never count as working days, even if they fall on a weekday that would normally count as per the
    '    UseDays argument
    ' 6) HolidayCol is the name of the column in the "holiday table" that contains the holiday dates
    Dim TestDate As Date                ' a date to be tested for being a working day or not
    Dim Moved As Long                   ' cumulative number of working days moved so far
    Dim x As Variant, y As Variant      ' used for looping through Holidays
    Dim DateStr                         ' used to construct key for Dictionary items
    Dim rs As DAO.Recordset
    Static HolidayDic As Object         ' stores holidays
    Static LastCall As Date             ' datetime for last function call
    ' As a Static variable, HolidayDic will persist in memory, which can be dangerous if there have
    ' been database updates since the last run.  Thus, test to see how long it's been since the last
    ' run.  If it's been 30 seconds or more, then force a rebuild of the dictionary to make sure we
    ' incorporate the most recent data available
    If DateDiff("s", LastCall, Now) >= 30 Then Set HolidayDic = Nothing
    LastCall = Now
    ' Build a Dictionary with the holidays in it.  The variable is Static, so it preserves its state in
    ' between calls (and thus this gets skipped if the Dictionary already exists
    If HolidayDic Is Nothing Then
        Set HolidayDic = CreateObject("Scripting.Dictionary")
        ' Skip populating the Dictionary if the table and/or column names are omitted
        If HolidayTbl <> "" And HolidayCol <> "" Then
            ' Make sure db object names are bracketed
            If Left(HolidayTbl, 1) <> "[" Then HolidayTbl = "[" & HolidayTbl
            If Right(HolidayTbl, 1) <> "]" Then HolidayTbl = HolidayTbl & "]"
            If Left(HolidayCol, 1) <> "[" Then HolidayCol = "[" & HolidayCol
            If Right(HolidayCol, 1) <> "]" Then HolidayCol = HolidayCol & "]"
            ' If the database objects do not exist, which would throw an error, then skip populating
            ' the Dictionary
            On Error Resume Next
            Set rs = CurrentDb.OpenRecordset("SELECT " & HolidayCol & " FROM " & HolidayTbl)
            If Err = 0 Then
                ' Loop through recordset and add Dictionary items as needed
                Do Until rs.EOF
                    DateStr = Format(rs.Fields(0).Value, "d mmm yyyy")
                    If Not HolidayDic.Exists(DateStr) Then HolidayDic.Add DateStr, rs.Fields(0).Value
            End If
            Set rs = Nothing
            On Error GoTo 0
        End If
    End If
    ' Initialize the TestDate
    TestDate = StartDate
    ' If the offset is 0, we have special handling to determine how the function behaves.  The special handling
    ' only applies if the start date is not a working day
    DateStr = Format(StartDate, "d mmm yyyy")
    If DayOffset = 0 And (InStr(1, UseDays, Weekday(StartDate)) = 0 Or HolidayDic.Exists(DateStr)) Then
        If ZeroMode > 0 Then
            DayOffset = 1
        ElseIf ZeroMode < 0 Then
            DayOffset = -1
            WorkdayXHolidayTbl = StartDate
            Exit Function
        End If
    ElseIf DayOffset = 0 Then
        WorkdayXHolidayTbl = StartDate
        Exit Function
    End If
    ' Starting with the start date, move forward or backward one day at a time from the start date, the
    ' direction determined by the sign of the DayOffset argument.  Each one-day movement only "counts"
    ' if the new date is a working day.  A working day is day for which its weekday index appears in
    ' the UseDays string, and its formatted date string does not appear in the HolidayStr variable
    If DayOffset < 0 Then
        Do Until Moved = Abs(DayOffset)
            TestDate = TestDate - 1
            DateStr = Format(TestDate, "d mmm yyyy")
            If InStr(1, UseDays, Weekday(TestDate)) > 0 And Not HolidayDic.Exists(DateStr) Then
                Moved = Moved + 1
            End If
        Do Until Moved = DayOffset
            TestDate = TestDate + 1
            DateStr = Format(TestDate, "d mmm yyyy")
            If InStr(1, UseDays, Weekday(TestDate)) > 0 And Not HolidayDic.Exists(DateStr) Then
                Moved = Moved + 1
            End If
    End If
    WorkdayXHolidayTbl = TestDate
End Function

Open in new window

Thus, in your query, you would use:

SELECT startDate, endDate, WorkdayXHolidayTbl(startDate, 5, "23456", 0, "[Holidays]", "[HolidayDt]") AS Workday
FROM date_test

Open in new window

white_stoneAuthor Commented:
Thank you very much Mathew.

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now