Comcatenate Help - Too many records being concatenated

[lmryckman]

Hi,
I am trying to concatenate some data and running into an issue...may be somthing silly, but I cannot figure it out.

Table data:
1-LTRNumber 3-PartNumber
LTR 0001 100 S MA11


LTR 0002 MA 100
LTR 0002 MA 200

What I WANT:
1-LTRNumber 3-PartNumber
LRT 0001 100 S MA11
LTR 0002 MA 100, MA 200

What I am getting:
1-LTRNumber 3-PartNumber
LRT 0001 100 S MA11, MA 100, MA 200
LTR 0002 100 S MA11, MA 100, MA 200

Query:
SELECT tblTestRequestParts.[1-LTRNumber], ConcatenatePartNumber([3-PartNumber]) AS PartNumber
FROM tblTestRequestParts;

Function ConcatenatePartNumber(pstrSQL As String, _
    Optional pstrDelim As String = ", ") _
    As String
    
    Dim rs As ADODB.Recordset
    pstrSQL = "SELECT [3-PartNumber] FROM [tblTestRequestParts] WHERE [3-PartNumber] Is Not Null"
    Set rs = CurrentProject.Connection.Execute(pstrSQL)
    Dim list As String
    list = rs.GetString(, , , pstrDelim)
    rs.Close
    Set rs = Nothing
    ConcatenatePartNumber = Left(list, Len(list) - Len(pstrDelim))

End Function

Thanks in advance for any help!
Laura

[pbaldy]

You would need to pass the 1-LTRNumber to the function and use it in the SQL as part of a criteria. As done here:

http://allenbrowne.com/func-concat.html

[lmryckman]

You would need to pass the 1-LTRNumber to the function and use it in the SQL as part of a criteria. As done here:

http://allenbrowne.com/func-concat.html

PERFECT!!!
Thank you so much...I have this working now.

[pbaldy]

Happy to help!