• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1747
  • Last Modified:

Return Select query result to string variable in Access 2007

I need to be able to run a SELECT query and return all values it finds to a string variable with the results seperated by commas using VBA. Can anyone help?
0
JP_TechGroup
Asked:
JP_TechGroup
2 Solutions
 
HainKurtSr. System AnalystCommented:
get the result as is from db and concatenate in the app

rs = cmd.open(sql)
whil not rs.eof
  if s="" then s = s & ", "& rs(0)
  if s<>"" then s = s & ", "& rs(0)
wend
0
 
Gustav BrockCIOCommented:
I would use GetRows.
From the on-line help:
Sub RækkerMatrix()
  Dim dbs As Database, rst As Recordset, strSQL As String
  Dim varRecords As Variant, intI As Integer, intJ As Integer

  Set dbs = CurrentDb
  strSQL = "SELECT * FROM tblYourTable"
  Set rst = dbs.OpenRecordset(strSQL)
  rst.MoveLast
  rst.MoveFirst
  varRecords = rst.GetRows(rst.RecordCount)
  For intI = 0 To UBound(varRecords, 2)
    Debug.Print
    For intJ = 0 To UBound(varRecords, 1)
      Debug.Print varRecords(intJ, intI)
    Next intJ
  Next intI
  rst.Close
  Set dbs = Nothing
End Sub

Open in new window


Adjust the line
      Debug.Print varRecords(intJ, intI)
to fit your need.

/gustav
0
 
EDDYKTCommented:
0
 
JP_TechGroupAuthor Commented:
Used data gathering from one and string handling for the other. Thanks guys.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now