-
Concat different rows column into single row field
Dear All,
There is a table
ID Name
---- -----
1 Peter
2 Mary
3 PauL
I would like to get all these three records and return in a single field, that is,
PeterMaryPaul
Is it possible to do with SQL in Access2000?
Thanks.
-
I dont know how to do this in SQL but you can do it in VBA. I created your table as table1. Then made a form with a text box, text0, and a command button, command1. I then added the following code to the command button:
Private Sub Command1_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strQry As String
Dim mystring As String
Set dbs = CurrentDb()
strQry = "SELECT * from table1"
Set rst = dbs.OpenRecordset(strQry)
rst.MoveFirst
Do While Not rst.EOF
mystring = mystring + rst.Fields("Name").Value
rst.MoveNext
Loop
Text0.SetFocus
Text0.Text = mystring
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing
End Sub
If this code does not run you need to check the visual basic editor has DAO references. Go to tools.refences and ensure that microsoft dao 3.x object library is ticked.
Hope this helps!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|