Array

[Mike]

I'm trying to pass the REG_Z_COUNTER in an Array Can some shed some light on
this for me
Thanks Mike
SQL1 = "SELECT TENDSALE.REG_Z_COUNTER " _
& "FROM TENDSALE " _
& "GROUP BY TENDSALE.REG_Z_COUNTER;"

[John Nurick]

Hi Mike,

I'm not sure what you mean by 'pass'. If you're trying to bind the
column TENDSALE.REG_Z_COUNTER to an array, you can't do that sort of
thing in VBA.

Things you can do, depending on what you're trying to achieve:

1) Open a recordset, e.g.
Dim R As DAO.Recordset
Set R = CurrentDB.OpenRecordset( _
"SELECT REG_Z_COUNTER FROM TENDSALE ORDER BY XXX;")
and then pass the recordset.

2) Open a recordset, iterate through it appending each value to an
array, e.g.
Dim A As String()
Dim R As DAO.Recordset
Dim j As Long

Set R = CurrentDB.OpenRecordset( _
"SELECT REG_Z_COUNTER FROM TENDSALE ORDER BY XXX;")
R.MoveLast
R.MoveFirst
Redim A(R.RecCount - 1)
For j = 0 to UBound(A)
A(j) = R.Fields(0).Value
R.MoveNext
Next
R.Close

and pass the array; later you can reverse the process and write values
back from the array into the table.

3) As for (2), but concatenate the values into a delimited string.

--
John Nurick - Access MVP

[Mike]

Thanks John