How do I concatenate records for one field 1

[levelhot]

I'm working on a Database App for Real Estate.
I need to take a table with one field and concatenate all the records with a comma delimiter.
i.e. createdstring = record#1.field1, record#2.field1, record#3.field1, etc.....
How can I accomplish this and then take the created string and use in an update query?
i.e. update field to "text"& createdstring &"text"
Anyone have any suggestions as I dont understand how to do this.

Thanks
LevelHot

 

[kphu]

Try this.


sub combinestring()

dim cdb as database, recs as recordset
dim intA, reccnt as integer
dim strA, strsql as string

set cdb = currentdb()
set recs = cdb.openrecordset("name of table or query&quot
recs.movelast
reccnt = recs.recordcount
recs.movefirst


for intA = 1 to reccnt
if recs.eof = true then
intA = reccnt + 1
else
strA = recs("field1&quot.value & ", " & strA
recs.movenext
end if
next intA

strsql = "INSERT INTO 'tablename'(Fieldname) VALUE "
docmd.runsql strsql & strA, 0

end sub

Note that the ending value of strA will be all the values in field1 with an ending of ,"space". We can get rid of that. Let me know if you run into any errors.

Ken

 

[levelhot]

Thanks alot. I actually found another piece of code that did the trick that is somewhat similar to your code module. At the same time it is doing exactly what I wanted