Filling an array from a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm sure this is fairly simple but I cannot find an example. I want to load
the records of a one field table into an array. The table contains only
dates and I already have all the code I need to compare the dates but don't
know how to load the array from the table.

Table - Holidays

1/17/05
2/12/05
5/30/05
etc....


redim Hol(Holidays.recordcount + 1)
For i = 1 to ? (Holidays.recordcount +1)
Hol(i) = record i of Holidays

next i

That is not the code I tried but pretty much what I need.
Thanks
Rich J
 
I have VB code in an Excel version of my program that counts the number of
holidays between two dates. This is necessary to project a completion date
and holidays need to be accounted for. Each project may have different
holidays. I already have a version that works well in Excel and simply want
to transfer most of the subroutines to Access. In Excel the person enters the
job specific holidays on a form that stores them on a worksheet. These are
permanently stored to be used repeatedly for udpates. In Access they would be
stored in a table and I need to simply load each record into an array to
perform the same operations. I already have the program working in both
Access and Excel except for this. I don't think a query would give me the
data handling I need for that.
I compare the holiday dates against the start date and count them between
the projected end date and then make a correction in the end date by that
count. It works perfectly in Excel.
 
air code, DAO assumed

dim yours() as date'at some module level
dim yoursCount as long'at the same level

dim rs as recordset
set rs=currentdb.openrecordset("yourtable")
rs.movelast
redim preserve yours(rs.recordcount)
yoursCount=1
rs.movefirst
do until rs.eof
yours(yoursCount) = rs!yourdatefield
yoursCount=yoursCount+1
rs.movenext
loop
rs.close
set rs=nothing
 
Thanks for your help. I haven't finished my additional coding yet, but I
just finished testing the coding you gave me and it works great. The program
I wrote is starting to get wide usage and I wanted to update the Access
version. Most are using it in Excel but that might change soon. Thanks
again. Rich J
 
Back
Top