Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 13th, 2014, 08:57 AM
Registered User
 
Join Date: Feb 2014
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Concatenate error

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
 
Old March 13th, 2014, 10:41 AM
Registered User
 
Join Date: Feb 2014
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Further attempts

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate help lryckman Access VBA 3 October 12th, 2009 08:52 AM
concatenate records davehodges Access 5 November 1st, 2007 10:13 AM
Hyperlink (Concatenate) JEHalm Excel VBA 1 January 11th, 2006 01:09 PM
Argh! Why does this concatenate? interrupt Javascript How-To 1 August 13th, 2005 08:44 AM
Concatenate numbers fs22 Javascript 1 April 25th, 2004 06:51 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.