Function to extract Holidays

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

Guest

I have a function that is working great to extract holidays. I've used the
function in the query expression below. My only problem is that when I run
the query a dialog box pops up and I have to type in the dates I want
extracted. I have a table called CatHolidays that I need as the
holidayarray. How do I state that my table is the array?

Thanks much -

EDSDate: IIf([MCSDate]="","",dhAddWorkDaysA([LTTP3],[MCSDate],[holidayarray]))
 
Alex said:
I have a function that is working great to extract holidays. I've
used the function in the query expression below. My only problem is
that when I run the query a dialog box pops up and I have to type in
the dates I want extracted. I have a table called CatHolidays that I
need as the holidayarray. How do I state that my table is the array?

Thanks much -

EDSDate:
IIf([MCSDate]="","",dhAddWorkDaysA([LTTP3],[MCSDate],[holidayarray]))
Click to expand...

I haven't really tried it, but if the function dhAddWorkDaysA expects an
array of dates as its third argument, you could write a function that
opens a recordset on the table, builds such an array, and returns it.
Something like this:

'----- WARNING: NOT FULLY TESTED -----
Function HolidayTableToArray(Optional fReset As Boolean) As Variant

Dim rs As DAO.Recordset
Static adtHolidays() As Date
Static fLoaded As Boolean

If fReset Then
fLoaded = False
End If

If Not fLoaded Then
Erase adtHolidays
Set rs = CurrentDb.OpenRecordset( _
"SELECT HolidayDate FROM CatHolidays", _
dbOpenSnapshot)
With rs
If Not .EOF Then
.MoveLast
.MoveFirst
End If
If .RecordCount > 0 Then
ReDim adtHolidays(.RecordCount - 1)
Do Until .EOF
adtHolidays(.AbsolutePosition) = !HolidayDate
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
fLoaded = True
End If

HolidayTableToArray = adtHolidays

End Function
'----- end of code -----

Given such a function, you could modify your query expression to call
it:

EDSDate:
IIf([MCSDate]="","",
dhAddWorkDaysA([LTTP3],[MCSDate],HolidayTableToArray()))

Note that I've given the function an optional argument, fReset. Because
the function builds a static array (for execution speed), you need this
mechanism to force the function to rebuild the array after you've added,
changed, or deleted records in the CatHoliday table.

An alternative to using this function, of course, would be to modify the
dhAddWorkDaysA function to open a recordset on the table itself, and use
that recordset instead of the array.
 
Back
Top