concatenate records in the same field (1 Viewer)

[SpiritedAway]

Hi All,

Strange question, I know, but just curious to know if it's possible to do it.

In my query I have a column where I can concatenate two fields:

ValidationReference: [City] & " " & "|" & " " & [Postocode]

However, If I wanted to concatenate data in just one field, say, [Postcode], how would I go about doing this?

So it would look something like this:

Column 1 | Column 2
London | Postcode 1 | Postcode 2 | Postcode 3

Ideally I would like to accomplish this in query design?

Any thoughts would be really useful

 

[Taruz]

Hi ..

Put a module in this function ..:

Public Function ConcatRelated(strField As String, _
    strTable As String, _
    Optional strWhere As String, _
    Optional strOrderBy As String, _
    Optional strSeparator = " | ") As Variant
On Error GoTo Err_Handler
    Dim rs As DAO.Recordset
    Dim rsMV As DAO.Recordset
    Dim strSql As String
    Dim strOut As String
    Dim lngLen As Long
    Dim bIsMultiValue As Boolean
    

    ConcatRelated = Null
    
    strSql = "SELECT " & strField & " FROM " & strTable
    If strWhere <> vbNullString Then
        strSql = strSql & " WHERE " & strWhere
    End If
    If strOrderBy <> vbNullString Then
        strSql = strSql & " ORDER BY " & strOrderBy
    End If
    Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
    bIsMultiValue = (rs(0).Type > 100)
    
    Do While Not rs.EOF
        If bIsMultiValue Then
            Set rsMV = rs(0).Value
            Do While Not rsMV.EOF
                If Not IsNull(rsMV(0)) Then
                    strOut = strOut & rsMV(0) & strSeparator
                End If
                rsMV.MoveNext
            Loop
            Set rsMV = Nothing
        ElseIf Not IsNull(rs(0)) Then
            strOut = strOut & rs(0) & strSeparator
        End If
        rs.MoveNext
    Loop
    rs.Close
    
    lngLen = Len(strOut) - Len(strSeparator)
    If lngLen > 0 Then
        ConcatRelated = Left(strOut, lngLen)
    End If

Exit_Handler:
    Set rsMV = Nothing
    Set rs = Nothing
    Exit Function

Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
    Resume Exit_Handler
End Function

A query can use this way ..:

select [City] & " | " & ConcatRelated("Postocode","table_name","City='" & [City] & "'") as PostCode
from table_name
group by City

 

[SpiritedAway]

Tarus - this works brilliantly thanks so much for your help on this.