Concatenating Values

[JimP]

I have a recordset that has multiple "CustID" values for a grouping. I want
to concatenate the "CustID" values into a string. I can do this via code by
opening the recordset and advancing through the records, creating a
concatenated string and adding each string to a table.

However, is there a way to do this directly in a query - some type of user
defined function?

 

[Brent Spaulding \(datAdrenaline\)]

Sure ... I use the function found here ....
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=1190033

Usage ...

MakeCSV("SELECT CustID FROM tblSomeTable WHERE SomeField = SomeValue")

Will yeild a string that looks like this ...

CustID1,CustID2,CustID3,CustID4.....

.....

The function is quite versatile in the sense that you can pass a SQL
Statement, ADO or DAO recordset, Table/Query, or List Box control. The post
I linked you to explains more. Also, if you have an issure with a
copy/paste from the site, just copy and paste into Word and replace the
manual line breaks (^l) with a paragraph (^p) .... more details on the
copy/paste are found here ...
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=53&Number=1406828

 

[JimP]

Brent,

Thanks very much for the code. One question,

The source table for my query has multiple groups, for which there are
multiple "CustID's" for each group.

I can see how the WHERE clause will filter a single group into the query,
but is there a way to handle multiple groups?

 

[Brent Spaulding \(datAdrenaline\)]

If I am following you, why would it not be feasible to just modify your
WHERE clause to filter out two groups? ...

=MakeCSV("SELECT CustID FROM tblCustomers WHERE GroupID In (1,2)")

or maybe concatenate two MakeCSV expressions together ...

=MakeCSV("SELECT CustID FROM tblCustomers WHERE GroupID = 1") & Chr(13) &
Chr(10) &
MakeCSV("SELECT CustID FROM tblCustomers WHERE GroupID = 2")

.....

Am I following you? or where you thinking something completely different?

 

[JimP]

Thanks, I'll give it a try.

If I am following you, why would it not be feasible to just modify your
WHERE clause to filter out two groups? ...

=MakeCSV("SELECT CustID FROM tblCustomers WHERE GroupID In (1,2)")

or maybe concatenate two MakeCSV expressions together ...

=MakeCSV("SELECT CustID FROM tblCustomers WHERE GroupID = 1") & Chr(13) &
Chr(10) &
MakeCSV("SELECT CustID FROM tblCustomers WHERE GroupID = 2")

....

Am I following you? or where you thinking something completely different?

 

[Brent Spaulding \(datAdrenaline\)]

Your welcome! ... Good Luck!