Combine Records

[depawl]

Hello:
Using Access 2003
I need to combine all the records from one field in a table to appear in one control on a report. For example if I have a table with 3 records, and a "Comments" field has the following data: good, bad, ugly
on the report I need:
Comments: good, bad, ugly
In one text box.
I tried using grouping to no avail and a bit of Googling came up with a concatenate function and lines and lines of code but I can't believe that it would be that difficult.
Thanks in advance for any assistance.

 

[Galaxiom]

Don't know if this is a practical solution for your real problem but it could work for the example you have given.
Make a CrossTab query to get the values from the required records into separate fields in the same record.
Then concatenate the values in those fields.

 

[AChir]

I had a similar problem and with help from the board ended up with:

Dim strList As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb

Set rs = db.OpenRecordset("tblMyTable")

' Concatenate the field I am interested in
Do While Not rs.EOF
    strList = strList & ", " & rs!MyField
    rs.MoveNext
Loop

' trim off the leading ", "
strList = Mid(strList, 3)
rs.Close
Set rs = Nothing

'Add the addresses to the appropriate textbox on the form 

Me.MyTextBox = strList

Hope it isn't too late to be of help

 

[depawl]

Thanks for the reply, but quite frankly it has been so long since I was working on this project (and I've done so many others since then) that I don't remember the specific issue. I assume that I must've gotten it to work the way I wanted.