Concatenate

[Dhamdard]

Dear friends,

I use following function to concatenate data from multiple rows into a single row/field. It puts a comma ',' after each concatenate. What I would like it to do more is to put 'and' instead of comma between the last two concatenated words. For example: Now it gives me Value 1 but I want to get Value 2.

Value 1:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs, community residents


Value 2:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs and community residents

Function:
Option Compare Database
Option Explicit

Public Function ConcatField(strSQL As String) As String

Dim dbs As Database
Dim rst As DAO.Recordset
Dim strConcat As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

With rst

If .RecordCount > 0 Then
Do Until .EOF
strConcat = strConcat & .Fields(0) & ", "
.MoveNext

Loop
End If

.Close

End With

Set rst = Nothing
Set dbs = Nothing

If Len(strConcat) Then
strConcat = Left(strConcat, Len(strConcat) - 2)
End If

ConcatField = strConcat

End Function

I did my tracks but couldn't make a progress. Your advise is highly appreciated.

Thanks,
Dad

 

[JHB]

Try the below code, (use the Code tag when you post code).:

Public Function ConcatField(strSQL As String) As String
  Dim dbs As Database
  Dim rst As DAO.Recordset
  Dim strConcat As String, strConcatLen As Long
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset(strSQL)
  
  With rst
  
    If .RecordCount > 0 Then
      Do Until .EOF
        strConcatLen = Len(strConcat)
        strConcat = strConcat & .Fields(0) & ", "
        .MoveNext
    
      Loop
    End If
    .Close
  End With
  
  Set rst = Nothing
  Set dbs = Nothing
  
  If Len(strConcat) Then
    strConcat = Left(strConcat, Len(strConcat) - 2)
    If strConcatLen > 0 Then
      strConcat = Left(strConcat, strConcatLen - 2) & Replace(strConcat, ", ", " And ", strConcatLen - 1)
    End If
  End If
  ConcatField = strConcat
End Function

 

[Dhamdard]

It worked perfectly. You are the HERO.