Can't assign values from query to array variable in Access

Question

I have a query OUTPUT with cols DATE, TYPE, VALUE. I need to gather all the different dates into a variable so I can later create a CSV file for each unique DATE. I tried creating a record set, and assigning the values of DATE to a variable as such:

Sub GetDates()
    Dim rst As Recordset
    Dim db As Database
    Dim arrDates As Variant

    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT Date FROM OUTPUT GROUP BY DATE; ")
    arrDates = rst.GetRows()

but when I do:

For Each Item In arrDates
    Debug.Print (Item)
Next

I only get one date printed (11/01/2012). If I run the query in Access, I see all 15 months.

Why not use the recordset, which is probably DAO (but you should specify) eg Do While Not rs.EOF ... Loop ? — Fionnuala
GetRows() fetches one row unless you tell it to fetch a different number of rows. See the help topic for details. — HansUp
I also would use SELECT DISTINCT Date FROM OUTPUT here because you are grouping by all outputted columns and you are not applying any aggregate functions. It just seems more natural to do so. — Olivier Jacot-Descombes
@HansUp: From Help: "If you do not specify a value for the Rows argument, the GetRows method automatically retrieves all the records in the Recordset object. If you request more records than are available, GetRows returns only the number of available records." — pedram

pedram pedram · Accepted Answer · 2014-04-08T17:24:11

I had to specify the number of rows: arrDates = rst.GetRows(rst.RecordCount)

A better solution, as recommended by Remou:

Do Until rst.EOF
    Debug.Print rst!Date
    rst.MoveNext
Loop

Edit: I was reading the wrong MSDN help.

Can't assign values from query to array variable in Access

1 Answers