Solved

create an array from a table in MS Access

Posted on 2011-09-18
4
480 Views
Last Modified: 2012-05-12
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
Holidays
8/1/2011
8/9/2011
8/18/2011


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.
0
Comment
Question by:white_stone
  • 2
4 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 36555984
Can we be totally clear about...

Where is the table?

Where are you running the code?

0
 

Author Comment

by:white_stone
ID: 36556007
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.
test-array.accdb
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36556077
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
                    rs.MoveNext
                Loop
                rs.Close
            Else
                Err.Clear
            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
        Else
            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
        Loop
    Else
        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
        Loop
    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

0
 

Author Closing Comment

by:white_stone
ID: 36556848
Thank you very much Mathew.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now