Results 1 to 9 of 9
  1. #1
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    146

    Concat BuildStr

    Hopefully someone will indulge me and download the attached database. I'm getting a syntax error: Syntax error in string in query expression 'INVNO=700001". I would think out of all the edits I've done I'd stumble on a solution, but no joy.

    Run Query: qry_Split_Ship_Msg

    In the end, I'm looking for results that look like the attached screenshot.



    Thanks!
    Attached Thumbnails Attached Thumbnails S1.jpg  
    Attached Files Attached Files

  2. #2
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    695
    I dont use ADO so heres a DAO version.

    Code:
    Public Function ConCatStr(strSql As String, fld As String) As String
    
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim strOut As String
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(strSql)
    
    
        If rs.BOF And rs.EOF Then
            ConCatStr = ""
            GoTo MyExit
        End If
    
    
        Do Until rs.EOF
            ConCatStr = ConCatStr & rs(fld) & vbNewLine
            rs.MoveNext
        Loop
    
    
    MyExit:
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    End Function
    The query would look like
    Code:
    SELECT DISTINCT TempOrderImport.INVNO, ConCatStr("Select ITEM2 from TempOrderImport where INVNO = '" & [INVNO] & "'","ITEM2") AS Expr1
    FROM TempOrderImport;
    note that you need to expand the height of the query grid to see the results.
    If this helped, please click the star at the bottom left and add to my reputation- Thanks

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,884
    Could build a report and set INVNO textbox HideDuplicates property to Yes.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    146
    Your code worked as expected. Much appreciated! I need to augment the output a bit and having syntax issues again. I added a column [WhsID]. To best illustrate what I need see below string where I added "Ships from [WhsID]". I've tried several combinations of ampersands, double and single quotes and just not getting it. Can you give me a clue how to add text and another field to the string?

    Msg: ConCatStr("Select ITEM2 Ships from [WhsID] from TempOrderImport where INVNO = '" & [INVNO] & "'","ITEM2")

    Thanks!

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,884
    Not clear to me what you are asking for. If you want additional text embedded in the concatenated string, should probably do that in the VBA procedure or in the main query, not the query that is used to pull the data for concatenation. Provide an example of what you want this output to look like.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    695
    Seeing as your using this in the context of a query, the function is designed to only concatenate one field.
    To add a field to your output I would think you'd add it to the query, not the sql of the function argument.
    If this helped, please click the star at the bottom left and add to my reputation- Thanks

  7. #7
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    146
    Attached is the revised table and screenshot for what I'm tying to accomplish. If possible, I'd rather edit the query to add text as it may change under certain conditions.

    Thanks!
    Attached Thumbnails Attached Thumbnails S1.jpg  
    Attached Files Attached Files

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,884
    One way to accomplish:

    SELECT DISTINCT TempOrderImport.INVNO, ConCatStr("Select ITEM2 & ' Ships from ' & WhsID & ' Warehouse' AS D from TempOrderImport where INVNO = '" & [INVNO] & "'") AS Msg
    FROM TempOrderImport;

    Change the VBA to:

    Public Function ConCatStr(strSql As String) As String

    ...

    ConCatStr = ConCatStr & rs(0) & vbNewLine

    If you want the literal text parts to be dynamic, probably should do this concatenation by referencing textbox with user input.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    146
    Perfect! I never would have gotten through that.

    Very much appreciated!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Concat textbox name with string
    By dwif in forum Access
    Replies: 2
    Last Post: 06-08-2013, 02:31 PM
  2. Last Name, First Name Concat Field
    By brianmcleer in forum Access
    Replies: 2
    Last Post: 06-05-2013, 12:18 PM
  3. Group concat
    By iostream in forum Queries
    Replies: 1
    Last Post: 02-23-2011, 12:29 PM
  4. Concat in SQL creates unwanted spaces
    By Deutz in forum Access
    Replies: 3
    Last Post: 12-07-2010, 11:43 PM
  5. concat problem
    By leahcim_32 in forum Access
    Replies: 1
    Last Post: 08-28-2009, 05:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums