Concatenate error

lmryckman · March 13th, 2014, 08:57 AM

I am getting an error and cannot figure it out. I think it is something little that is going to make me feel silly...but it is driving me nuts!

Run-time error '-2147217900 (80040e14)':
Invalid SQL statement; expected 'Delete', 'Insert', 'Procedure', 'Select', or 'Update'.

It stops on this line...
Set rs = CurrentProject.Connection.Execute(pstrSQL)

When I put my cursor over pstrSQL, it shows pstrSQL = "MA 100" (which is the value of the first record in the field [3-PartNumber].

rs is still equal to Nothing...of course because of the error.

Any ideas??
Thanks in advance!

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

Code:

Function ConcatenatePartNumber(pstrSQL As String, _
    Optional pstrDelim As String = "', ") _
    As String
    
    Dim rs As ADODB.Recordset
    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

lmryckman · March 13th, 2014, 10:41 AM

I added an SQL statement that it seems to want (although I didn't do this in my other database...maybe a 2007 vs 2013 thing?? I am using 2007 for this.) but there is a flaw...

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

My revised code...

Code:

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