How to pass only a single dimension of an array?

[prabha_friend]

Hi all,
Thank you for your answers. Now the getrows is working fine at my end. Now I have a different problem at hand:

Sub Test2()
Dim myrset As Recordset
Set myrset = CurrentDb.OpenRecordset("SELECT * FROM Holidays;")
myrset.MoveLast
myrset.MoveFirst
MsgBox myrset.RecordCount
MsgBox excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, Array(#8/15/2014#, #8/29/2014#))
MsgBox excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, excel.Application.WorksheetFunction.Index(myrset.GetRows(myrset.RecordCount), 0, 1))
End Sub

Recordcount is coming fine
Manual Array is also giving the right result
But the Index method is giving the wrong value. Why?

(Holidays table is just having the values in the array only ie. #08/15/2014# and #08/29/2014#)

 

[pr2-eugin]

Here try this,

Sub PrabhaCode2()
    Dim myRS As Recordset
    Dim recCount As Long
    
    recCount = CurrentDb.OpenRecordset("SELECT Count(*) FROM Holidays " & _
                                       "WHERE DateFieldName BETWEEN #08/01/2014# AND #08/31/2014#;", dbOpenSnapshot, dbReadOnly).Fields(0)
                                       
    Set myRS = CurrentDb.OpenRecordset("SELECT CDbl(DateFieldName) FROM Holidays " & _
                                       "WHERE DateFieldName BETWEEN #08/01/2014# AND #08/31/2014#;")
                                       
    MsgBox Excel.Application.WorksheetFunction.NetworkDays(#8/1/2014#, #8/31/2014#, myRS.GetRows(recCount))
End Sub

 

[vbaInet]

What makes you so sure that the Index() function is returning the wrong result? Did you test it on it's own?

You're working with an array, you don't need an intermediary Index() function:

MsgBox excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, myrset.GetRows(myrset.RecordCount)(0,1))

And I believe that the Networkdays function only works with one-dimensional arrays so the above won't work.

 

[prabha_friend]

Here try this,

Sub PrabhaCode2()
    Dim myRS As Recordset
    Dim recCount As Long
 
    recCount = CurrentDb.OpenRecordset("SELECT Count(*) FROM Holidays " & _
                                       "WHERE DateFieldName BETWEEN #08/01/2014# AND #08/31/2014#;", dbOpenSnapshot, dbReadOnly).Fields(0)
 
    Set myRS = CurrentDb.OpenRecordset("SELECT CDbl(DateFieldName) FROM Holidays " & _
                                       "WHERE DateFieldName BETWEEN #08/01/2014# AND #08/31/2014#;")
 
    MsgBox Excel.Application.WorksheetFunction.NetworkDays(#8/1/2014#, #8/31/2014#, myRS.GetRows(recCount))
End Sub

Paul. I wonder how it is working fine as they have mentioned in the help that the GetRows will always return a 2D Query. Don't we have to split the dimensions before passing it to the Networdays(). Now, Even the following is working:
MsgBox Excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, myrset.GetRows(myrset.RecordCount))

 

[pr2-eugin]

Well that is strange. I am not sure how arrays are passed. I will have a read through, in the mean time, why not create a 1D array loop the values in the recordset and then pass this array?

Or other option is, why not simply use one of the "Find Business Days" available all over the internet to find the business days? I am sure there is one by Pat Hartman on this site, which seems to be quiet reliable. She has listed quiet a few Date functions. Here is the link to it : http://www.access-programmers.co.uk/forums/showthread.php?t=238821

 

[vbaInet]

Actually it makes no mention of any dimensions. So 1 or 2 dimensions is a valid argument. It'll just iterate through it.

 

[prabha_friend]

Actually it makes no mention of any dimensions. So 1 or 2 dimensions is a valid argument. It'll just iterate through it.

Hi VbaInet,
Don't take me annoying. Please read the following:

Remarks
Use the GetRows method to copy records from a Recordset.GetRows returns a two-dimensional array. The first subscript identifies the field and the second identifies the row number.

its found in the help document for getrows method. I have one more doubt. When I pressed the F1 key focus on the Getrows keyword I got help suggestion for two methods:

Recordset.GetRows Method
Microsoft Data Access Objects (DAO) Reference > DAO Reference > Recordset Object > Methods


Recordset2.GetRows Method
Microsoft Data Access Objects (DAO) Reference > DAO Reference > Recordset2 Object > Methods


What is that Recordset2? I am getting like this for many objects...

 

[vbaInet]

I'm talking about the NetworkDays function.