Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010

    Unanswered: How to Concatenate Rows from One Table

    I have seen some examples (How to concatenate multiple child records into a single value - Microsoft: Access Queries and JET SQL FAQ - Tek-Tips) of how to concatenate rows together but have yet to make it work.

    I have one table - a master email table - that houses all client ID's and email addresses. Example would be:

    ClientID - Email

    1 -
    1 -
    2 -
    3 -

    I need to concatenate these values based on clientID to send an email (versus sending the same email multiple times with one 'to' address.

    So ideally I would like to have something like this:
    for clientID = 1 would return;

    It seems like the attached would handle it but for some reason Access 2007 doesn't seem to recognize the concatenate() function.

    Any ideas on how to do this?


  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    That link doesn't work for me. There are also

    Modules: Return a concatenated list of sub-record values

    Microsoft Access tips: Concatenate values from related records

    Both of which (and probably yours too) require the function to be in a standard module.

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 14
    You can adapt the following example. Replace the names of the table and of the columns in strSQL to adapt it to your database.
    [CODE]Function RowConcat(ID As Long) As String

    Dim strSQL As String
    Dim rst As DAO.Recordset
    Dim str As String

    strSQL = "SELECT Category FROM dbo_V_Order_Details " & _
    "WHERE PK_CF_Data = " & ID
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    With rst
    Do Until .EOF
    If Len(Nz(!Category, "")) > 0 Then
    If Len(str) > 0 Then str = str & ";"
    str = str & !Category
    End If
    End With
    Set rst = Nothing
    RowConcat = str

    End Function[/CODE
    Have a nice day!

  4. #4
    Join Date
    May 2014

    Smile Thank you!

    Worked great!

  5. #5
    Join Date
    Mar 2009
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts