Define a table as an array

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

Guest

How do I define my table "holidays" containing a column "Date" as an array so
that I can use it in the following function?

Thanks -

Example:
Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
' Add the specified number of work days to the
' specified date.

' Modified from code in
' "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.

' In:
' lngDays:
' Number of work days to add to the start date.
' dtmDate:
' date on which to start looking.
' Use the current date, if none was specified.
' adtmDates (Optional):
' Array containing holiday dates. Can also be a single
' date value, if that's what you want.
' Out:
' Return Value:
' The date of the working day lngDays from the start, taking
' into account weekends and holidays.
' Example:
' dhAddWorkDaysA(10, #2/9/2000#, Array(#2/16/2000#, #2/17/2000#))
' returns #2/25/2000#, which is the date 10 work days
' after 2/9/2000, if you treat 2/16 and 2/17 as holidays
' (just made-up holidays, for example purposes only).

' Did the caller pass in a date? If not, use
' the current date.
Dim lngCount As Long
Dim dtmTemp As Date

If dtmDate = 0 Then
dtmDate = Date
End If

dtmTemp = dtmDate
For lngCount = 1 To lngDays
dtmTemp = dhNextWorkdayA(dtmTemp, adtmDates)
Next lngCount
dhAddWorkDaysA = dtmTemp
End Function
 
There are several ways to get table data and store it into an array. The
first is the easiest way.

Create a variant variable to act as your array. Create a recordset then
simply use the recordset command "GetRows" to populate your variant array.

dim db as database
dim rs as recordset
dim vArr as variant
dim lRecordCount as long
dim lRow as long
dim lCol as long
dim lCols as long

set db = currentdb
set rs = db.openrecordset([SQL Expression or Table Name], dbopendynaset)

if(not rs.eof) then
rs.movelast
rs.movefirst
lRecordCount = rs.recordcount
vArr = rs.GetRows(lRecCount)
end if

.....

<Or>
if(not rs.eof) then
rs.movelast
rs.movefirst
lRecordCount = rs.recordcount
lCols = rs.fields.count-1
Redim vArr(lCols, lRecordCount-1)
For lRow = 0 to lRecordCount-1
For lCol = 0 to lCols
vArr(lCol, lRow) = rs.fields(lCol)
Next lCol
rs.movenext
Next lRow
end if

You will need to add error trapping and any other necessary code. However,
this is the short and simple of storing table data into an array.

Best of luck
 
Back
Top