Combine values from match records into string 1

[mckenneyj]

Need to delevop a query that will take records where "Status" = Current and concatenate the "Designations" for each "UserID" into a single string

i.e. final outout for table below should be:
456 ABC, CBS
345 CBS, XYZ

Table with 3 fields:
UserID, Status, Designation

123 Lost ABC
456 Current ABC
345 Current CBS
345 Current XYZ
765 Lapse JVC
456 Current CBS

Thanks,
John McKenney
Work Hard... Play Harder

 

[PHV]

Please, read the FAQs before posting.

http://www.tek-tips.com/faqs.cfm?fid=4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mckenneyj]

The article you refer to has multiple compile errors

Thanks,
John McKenney
Work Hard... Play Harder

 

[PHV]

So, what have you so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mckenneyj]

I tossed the funtion because it would not compile as the recordset was not defined properly in the function.

table name is tblHolders

Thanks,
John McKenney
Work Hard... Play Harder

 

[PHV]

Please, feel free to come back with what you tried and where you're stuck.
Often recordset problems are related to a lack of referencing the Microsoft DAO object library and not strongly typing (ie Dim rst As DAO.Recordset).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mckenneyj]

The compile error occurs on this line:
Dim rs As New ADODB.Recordset

The error is: User defined type not defined

I am referencing DAO 3.6


Thanks,
John McKenney
Work Hard... Play Harder

 

[PHV]

So you have to reference Microsoft ActiveX Data Object for ADO animals.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244