how to aggragate a column of records?

[mwcrepeau]

I have a column of values in a query-generated table that I'd like to aggragate into a comma-delimited list. So the query result looks something like this:

ID Data
1 Item1
2 Item2
3 Item3
.
.
.
n Itemn

Since they are drawn from a query the ID numbers aren't necessarily an unbroken series, and n will vary from run to run. What I want is a string like this:

Item1,Item2,Item3,...Itemn

I'm having trouble figuring out how to iterate through the records from the top of the column to the bottom to string together the values. Anyone have any suggestions? Thanks!

 

[ajetrumpet]

Iterate through, and use the result wherever need be:

dim rs as recordset, i as integer, strResult as string

  set rs = currentdb.openrecordset("tablename")

strresult = ""

with rs

  .movefirst

do until .eof
   strresult = strresult & !data & ", "
      .movenext
loop

strresult = left(strresult, len(strresult) - 2

use STRresult whereever you need the string at.

 

[mwcrepeau]

Yes. That works great. Thanks!

 

[ajetrumpet]

np glad to help.