Concat different rows column into single row field

[geossl]

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.

[galileeian]

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!