By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,146 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Troubles with query into array

P: 32
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...

Expand|Select|Wrap|Line Numbers
  1. Dim Rept As DAO.Recordset
  2. Dim Rcount As DAO.Recordset
  3. Dim Report As Variant
  4. Dim sql1 As String
  5. Dim db As Database
  6.  
  7.  
  8. sql1 = "select rpt from scarlet where rpt <> '~' group by rpt"
  9. Set db = CurrentDb
  10. Set Rept = db.OpenRecordset(sql1)
  11.  
Mar 19 '08 #1
Share this Question
Share on Google+
4 Replies


JustJim
Expert 100+
P: 407
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...

Expand|Select|Wrap|Line Numbers
  1. Dim Rept As DAO.Recordset
  2. Dim Rcount As DAO.Recordset
  3. Dim Report As Variant
  4. Dim sql1 As String
  5. Dim db As Database
  6.  
  7.  
  8. sql1 = "select rpt from scarlet where rpt <> '~' group by rpt"
  9. Set db = CurrentDb
  10. Set Rept = db.OpenRecordset(sql1)
  11.  
Hi,

Try replacing your sql1 string with the following
Expand|Select|Wrap|Line Numbers
  1. SELECT scarlet.rpt, Count(scarlet.rpt) AS [GradeCount]
  2. FROM scarlet
  3. GROUP BY scarlet.rpt
  4. HAVING (((scarlet.rpt)<>"~"))
and working with the GradeCount as well as rpt.

Jim
Mar 20 '08 #2

P: 32
Hi,

Try replacing your sql1 string with the following
Expand|Select|Wrap|Line Numbers
  1. SELECT scarlet.rpt, Count(scarlet.rpt) AS [GradeCount]
  2. FROM scarlet
  3. GROUP BY scarlet.rpt
  4. HAVING (((scarlet.rpt)<>"~"))
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
Mar 20 '08 #3

P: 32
Nevermind I figured it out, Thanks!
Mar 20 '08 #4

JustJim
Expert 100+
P: 407
Nevermind I figured it out, Thanks!
Glad you got it. Fun isn't it?

Jim
Mar 21 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.