Concatenate fields?

[chrisgreen]

I have a table with :

ActivityNo, lineNo and comments

ActivityNo and LineNo is the key.

The comment is spead in the table accross the lineNo with the same activityNo. I want to produce a query that has the fields ActivityNo and comments. To do this I need to concatenate all the comments, how do I do this?

 

[DougP]

Expr1: [ActivityNo] & " " & [comments]

I put a blank sapce in between the two also.

"Expr1" can be any word you like that makes sense.

DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[Mats]

Set the rowsource type to value list with two columns and set this procedure somewhere so that it will be run every time the data in the combo should change. The code might not be perfect, but you should get the idea. If you test it, it should return something like ActivityNO;Comment,Comment;ActivityNo;Comment,Comment...

Dim rst as recordset
Dim str as string
dim i as integer (or whatever type ActivityNo is)
str = "SELECT * FROM YOURTABLE ORDER BY ActivityNo, LineNo"
set rst=currentdb.openrecordset(str,dbopensnapshot)

if not rst.EOF then
rst.movefirst
i=0
do until rst.EOF=true
if i= rst("ActivityNo&quot then
str= str & "," & rst("Comments&quot
else
str = str & ";" & rst("ActivityNo&quot & ";" & rst("Comments&quot
end if
i = rst("ActivityNo&quot
rst.movenext
loop
end if

YourCombo.rowsource = right(len(str)-1) & ";"
rst.close
set rst =nothing

Hope this helps,

Mats

BTW this is for DAO, wont work in Access 2000 without some small changes...