Filling an array from a table

R

Rich J

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
 
R

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.
 
B

Bas Cost Budde

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
 
R

Rich J

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
 
Top