Combine rows / concatenate rows

by

Here is a sample User Defined Function (UDF) and possible usage.

Function:

Function Coalsce(strSQL As String, strDelim, ParamArray NameList() As Variant)
Dim db As Database
Dim rs As DAO.Recordset
Dim strList As String

    Set db = CurrentDb

    If strSQL <> "" Then
        Set rs = db.OpenRecordset(strSQL)

        Do While Not rs.EOF
            strList = strList & strDelim & rs.Fields(0)
            rs.MoveNext
        Loop

        strList = Mid(strList, Len(strDelim))
    Else

        strList = Join(NameList, strDelim)
    End If

    Coalsce = strList

End Function

Usage:

SELECT documents.MembersOnly, 
    Coalsce("SELECT FName From Persons WHERE Member=True",":") AS Who, 
    Coalsce("",":","Mary","Joe","Pat?") AS Others
FROM documents;

An ADO version, inspired by a comment by onedaywhen

Function ConcatADO(strSQL As String, strColDelim, strRowDelim, ParamArray NameList() As Variant)
   Dim rs As New ADODB.Recordset
   Dim strList As String

   On Error GoTo Proc_Err

       If strSQL <> "" Then
           rs.Open strSQL, CurrentProject.Connection
           strList = rs.GetString(, , strColDelim, strRowDelim)
           strList = Mid(strList, 1, Len(strList) - Len(strRowDelim))
       Else
           strList = Join(NameList, strColDelim)
       End If

       ConcatADO = strList

   Exit Function

   Proc_Err:
       ConcatADO = "***" & UCase(Err.Description)
   End Function

From: http://wiki.lessthandot.com/index.php/Concatenate_a_List_into_a_Single_Field_%28Column%29

More Related Contents:

  1. ms access vba code for update records through form
  2. How to add default signature in Outlook
  3. Is it possible to pass parameters programmatically in a Microsoft Access update query?
  4. Getting Error 3340 Query ‘ ‘ is corrupt while executing queries DoCmd.RunSQL
  5. Too few parameters Expected 1, recordset issue
  6. Show “Open File” Dialog
  7. How do I display a messagebox with unicode characters in VBA?
  8. Determining whether an object is a member of a collection in VBA
  9. Distinction between using .text and .value in VBA Access
  10. MS Access VBA Error: Run time error ’70’ Permission Denied
  11. Automatically generating handling of issues
  12. Is it better to use ADO or DAO in Access 2007?
  13. VBA – Folder Picker – set where to start [duplicate]
  14. MS Access: how to compact current database in VBA
  15. How to loop through all controls in a form, including controls in a subform – Access 2007
  16. FileDialog doesn’t work
  17. Working with Unicode file names in VBA (using Dir, FileSystemObject, etc.)
  18. Determine real cause of ODBC failure (error 3146) with ms-access?
  19. Is there an equivalent to Thread.Sleep() in VBA
  20. Exporting MS Access Forms and Class / Modules Recursively to text files?
  21. How to get logged-in user’s name in Access vba? [duplicate]
  22. Pass a parameter into an access report programmatically
  23. finding latest file in a folder and opening it (vba access)
  24. Insert multiple records with a date range in MS Access
  25. MS Access VBA: Sending an email through Outlook [closed]
  26. SendKeys is messing with my NumLock key via VBA code in Access form
  27. Runtime error 287. Sending Emails through Outlook using VBA in Access
  28. VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds
  29. Round function in Excel, worksheet function vs VBA
  30. Does the bitness of the OS ever matter, or is it just the application I need to worry about?

Leave a Comment