Troubles with query into array

[bhughes2187]

I am using access 2003, I am querying the table scarlet using a statement

sql1 = "select rpt from scarlet where rpt <> '~' group by rpt"

basically, rpt will have values of ~, A, B, C, D. A can be assigned to 10 records, B to 5 records, etc...

sql1 should retrieve all the A,B,C,D and group them together so that the result of the query would give me A B C D.

What I want to do is take those values and insert them into an array for later use. I am having a hard time finding a solution...

Dim Rept As DAO.Recordset
Dim Rcount As DAO.Recordset
Dim Report As Variant
Dim sql1 As String
Dim db As Database


sql1 = "select rpt from scarlet where rpt <> '~' group by rpt"
Set db = CurrentDb
Set Rept = db.OpenRecordset(sql1)

[JustJim]

I am using access 2003, I am querying the table scarlet using a statement

sql1 = "select rpt from scarlet where rpt <> '~' group by rpt"

basically, rpt will have values of ~, A, B, C, D. A can be assigned to 10 records, B to 5 records, etc...

sql1 should retrieve all the A,B,C,D and group them together so that the result of the query would give me A B C D.

What I want to do is take those values and insert them into an array for later use. I am having a hard time finding a solution...

Dim Rept As DAO.Recordset
Dim Rcount As DAO.Recordset
Dim Report As Variant
Dim sql1 As String
Dim db As Database


sql1 = "select rpt from scarlet where rpt <> '~' group by rpt"
Set db = CurrentDb
Set Rept = db.OpenRecordset(sql1)

Hi,

Try replacing your sql1 string with the following
[CODE=sql]SELECT scarlet.rpt, Count(scarlet.r pt) AS [GradeCount]
FROM scarlet
GROUP BY scarlet.rpt
HAVING (((scarlet.rpt) <>"~"))[/CODE]
and working with the GradeCount as well as rpt.

Jim

[bhughes2187]

Hi,

Try replacing your sql1 string with the following
[CODE=sql]SELECT scarlet.rpt, Count(scarlet.r pt) AS [GradeCount]
FROM scarlet
GROUP BY scarlet.rpt
HAVING (((scarlet.rpt) <>"~"))[/CODE]
and working with the GradeCount as well as rpt.

Jim

Using this method in my VBA code, I get syntax errors.

Once I have this query run, how exactly do I get it into an array.. Thats my main thing to know

[bhughes2187]

Nevermind I figured it out, Thanks!

[JustJim]

Nevermind I figured it out, Thanks!

Glad you got it. Fun isn't it?

Jim