Problem with Array

[Candace]

I was trying to calculate the number of days excluding
weekends and holidays. I was using the code from this site
http://www.mvps.org/access/datetime/date0012.htm

Now to read in the holidays I used this function
FillIndefArray which was taken from the KnowledgeBase then
modified

My problem is reading in the holidays. I have a table of
the holidays and I try to make it into an array, like the
example, in the function FillIndefArray.

Structure
dhCountWorkdaysA2(ByVal dtmStart As Date, ByVal dtmEnd As
Date, Optional adtmDates As Variant) As Integer
Example
dhCountWorkdaysA(#7/2/2000#, #7/5/2000#, Array(#1/1/2000#,
#7/4/2000#))

It's not processing the array and I think it's because it
has double quotations around the array like "Array
(#1/1/2000#, #7/4/2000#)". I realize this happens because
I have it declared as a variant but I don't know how to
make it stop.

Any ideas?

Candace

Function FillIndefArray()
Dim dbSample As DAO.Database
Dim rstSample As DAO.Recordset
Dim intArrayCount As Integer
Dim aryTestArray() As Variant
Dim intCounter As Long
Dim StFilter As Variant

Set dbSample = CurrentDb()
Set rstSample = dbSample.OpenRecordset("Holiday")
intArrayCount = 0
ReDim Preserve aryTestArray(0)

' Fill the array.
With rstSample
StFilter = "Array("
.MoveFirst
Do Until rstSample.EOF
If (Forms![Startup]![Last_YTD_Start] <= !
[Holiday_Stat_Date]) And (Forms![Startup]![Q4_End] >= !
[Holiday_Stat_Date]) Then
' Fill the array row with the stat date.
aryTestArray(intArrayCount) = !
[Holiday_Stat_Date]
StFilter = StFilter & "#" & !
[Holiday_Stat_Date] & "#, "
' Increase the number of elements in the array
' by one to accommodate the next record.
ReDim Preserve aryTestArray(UBound
(aryTestArray) + 1)
intArrayCount = intArrayCount + 1
End If
.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve aryTestArray(UBound(aryTestArray) - 1)
StFilter = Left(StFilter, Len(StFilter) - 2)
StFilter = StFilter & ")"
.Close
End With
dbSample.Close

FillIndefArray = StFilter

End Function

 

[Emilia Maxim]

Candace,

Structure
dhCountWorkdaysA2(ByVal dtmStart As Date, ByVal dtmEnd As
Date, Optional adtmDates As Variant) As Integer
Example
dhCountWorkdaysA(#7/2/2000#, #7/5/2000#, Array(#1/1/2000#,
#7/4/2000#))

It's not processing the array and I think it's because it
has double quotations around the array like "Array
(#1/1/2000#, #7/4/2000#)". I realize this happens because
I have it declared as a variant but I don't know how to
make it stop.

You're right about those double quotations. Having a variant as a
parameter which is supposed to contain an array, you must pass the
_result_ of the Array function, which is an array. If you pass the
function name as a string, then the variant adtmDates will contain
just that - i.e. a string. Please note: I can only assume this, there
is a missing link in your post between dhCountWorkdaysA and
FillIndefArray.

Do you really have explicit values in the code? If so, I guess the
compiler would throw an error when passing a value like #7/5/2000#
without quotes. Also, if the parameter are Date type, you could pass
the dates in some variables (or control values or whatever), and do
the formatting only when concatenating them in an SQL (or WHERE clause
or whatever). I don't think VBA likes those gatter characters.

Function FillIndefArray()

Hm, I'd treat the array Redim handling a bit simpler. Further, if you
explicitly fill an array, why bothering about returning an Array
function which must be further processed?

intArrayCount = 0

' Fill the array.
With rstSample
.MoveFirst
Do Until rstSample.EOF
If (Forms![Startup]![Last_YTD_Start] <= !
[Holiday_Stat_Date]) And (Forms![Startup]![Q4_End] >= !
[Holiday_Stat_Date]) Then
'Make place for the start date and
' fill it with the start date in SQL format.
ReDim Preserve aryTestArray(intArrayCount)
aryTestArray(intArrayCount) ="#" &
![Holiday_Stat_Date] & "#"
' Increase the array index for the next Redim
intArrayCount = intArrayCount + 1
End If
.MoveNext
Loop
.Close
End With

'Don't close what you didn't open
'dbSample.Close

'Return the array with dates
FillIndefArray = aryTestArray

End Function

HTH

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de

 

[Can]

Thanks for the help Emilia.

I was still have problems when I made those suggestions so
I traced through the code and for the lines with
aryTestArray(intArrayCount) it says <subscript out of
range>

How do I fix that? Is it not recognizing the end of file?

Candace

-----Original Message-----

ReDim Preserve aryTestArray(intArrayCount)
aryTestArray(intArrayCount) ="#" &![Holiday_Stat_Date] & "#"

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
.