DLookupPlus.

Class: Access Developer 17
Code Vault: DLookupPlus

Hi John, this seems quite a popular topic, as I've answered this query a few times in recent weeks, and twice in the past 24 hours.   Basically you need to write a custom function in VBA that reads through the necessary records with a Recordset loop and construct a comma-delimited string from those records.  Then you use this custom function in a query or other VBA code to get the result you desire.  My pictures show an example of this.  I remember you said you were also an SQL Server user.  SQL Server has a (much) easier way to do this, shown in my second pic below (done with a "passthrough query" in Access).  I don't have access to Richard's Code Vault, so the methods cited by Kevin above may be different from mine.

Dear Robertson,
I used the code vault and gave Error in the field , any idea ?

Dear Yip,
Do you have a simpler custom function in VBA ? I just have 2 columns to combine, no dates, no units to add
Really is like above column A and column B
Thanks again

I can't think of a simpler function or methodology.  And some of the code from the code vault is not straightforward either.  You often can't just use other people's code verbatim, and have to adapt from it.

No matter how simple your table's structure is, my method still requires using a Recordset loop, processing each record, adding commas after them, and checking for empty values before adding commas.  A function with only ten lines is not the most complex of functions.  If you use SQL Server, then my second method is the simplest, with only one SQL statement.

Hi John,

I've put together a screenshot using the sample data in your initial post as an example of how to set this up.

done. thanks Robertson. you are just great

thanks Yip too. I really appreciate your help but I am not that smart to transpose your code to use.

John, you're a gold member. Use the DLookupPlus function.