How do list results of a column in a table in one field separated by commas in MS Access

Question

I would like to know how to get a column into a query field. Similar to how in Access if you use the multi-value field in MS Access. However, i do not wish to use the mutli-value.

For example, if I had a table

column1 with
A
C
D
F
G

(one letter in each row under Colmn1).

and I wanted to display it in a query field like: A, C, D, F, G

How could i achieve this?

Answer 1

Hi,

Allen Browne has a ready made function to do it:

http://www.allenbrowne.com/func-concat.html

Servus
Karl

---

Access Forever, News, DevCon
Access-Entwickler-Konferenz AEK - 19./20.10. Nürnberg

Answer 2

You might also like to take a look at Concat.zip in my public databasse folder at:

https://onedrive.live.com/?id=44CC60D7FEA42912%21169&cid=44CC60D7FEA42912

This includes an illustration of the use of a GetList function which calls the highly efficient GetString method of the ADO Recordset object to concatenate values in a column into a single delimited string expression. The illustration of the function in the demo uses a column from a related table to return a subset of values from the column, but you can use it on a single table simply by omitting the final optional strFilter argument when calling the function.

Answer 3

Thanks to the both of you for providing a response, I believe this is what I've seen before. I just lost the notes on it. Once again, thanks.