none
Combine multiple rows from a query into 1 row string.

    Question

  • Thanks for all of your help.  Here is what i have:

    query #1: 11 columns of data pulled from tables.

    query #2: query 1, all 11 columns combined into 1 column. Query #2 sql:

    SQL: SELECT [Expr1] & "" & [PN] & "" & [Expr2] & "" & [Desc] & "" & [Expr3] & "" & [TQY] & "" & [Expr4] & "" & [Expr5] & "" & [Expr7] & "" & [Expr8] AS PList
    FROM PartsListQ; 

    I need to put all of the rows from query 2 into 1 row as a string without a header that i can export with VBA.  I have everything working except combining the rows from query 2 into 1 row as a string.  There is more than 255 charactors in each row. I think this should be simple, but i am not understanding any of the examples i have found in my searches.  All of the examples show adding some data from different rows into 1 row.  I need all of the rows combined into 1.

    Thank you for helping. Jerry215

    Monday, November 27, 2017 1:24 PM

Answers

All replies

  • Check this out: http://theaccessweb.com/modules/mdl0004.htm


    -Tom. Microsoft Access MVP

    Monday, November 27, 2017 1:51 PM
  • Thank you.

    I looked at the link and copied and tried to set up so i could use it. Most of the function i don't need because the query already has all of the records i need so i don't need to load by Id number or any other fields, i just need to put all of the rows in the query into 1 long string.  I have searched for a few days trying to find something close to what i need. I looked at Allen Brown's Concatenate and many others in the help search. All of these are taking some records from different rows depending on an Id number and putting them all in a string.  I don't need to get some records from rows, i need all of the data in each row combined into a string. Sorry, i don't know how else to explain it. I'm frustrated and i would pull out my hair if i had any. I can't have any separators in the string.  This is what i have come up with so far:

    ' PartsListQ2 has 1 field [PList], all of the records come from another query, and all fields are combined into 1 field in PartsListQ2 query.

    Public Function MakeTextFile(PList As String) As String
    On Error GoTo MakeTextFile_Err
    Dim db As DAO.Database, rs As DAO.Recordset
    Dim myPartsList As String

        myPartsList = Null
        Set db = CurrentDb
        Set rs = db.OpenRecordset("PartsListQ2", dbOpenSnapshot)

        With rs
            If .RecordCount <> 0 Then
                Do While Not rs.EOF
                    myPartsList = myPartsList & rs(PList)
                    .MoveNext
                Loop
            End If
        End With
                  
        Set rs = Nothing
        Set db = Nothing
       
        Call CreatXML                                               ' Call the function to make the master parts list and export it.
       
    MakeTextFile_Exit:
        Exit Function
    MakeTextFile_Err:
        MsgBox Error$
        Resume MakeTextFile_Exit
    End Function

    Thank you for any help you can give.

    Monday, November 27, 2017 7:50 PM
  • Hi,

    Just curious, did you happen to run into this simple function in your searches?

    To combine all the fields in a record and all the records in a table - without any separators, you could try something like:

    SimpleCSV("SELECT Field1 & Field2 & Field3 & Field4 FROM TableName","")

    Hope it helps...

    Monday, November 27, 2017 8:10 PM
  • Hi Jerry215,

    I want to confirm with you that, do you want to combine rows into a row or combine or fields into a field?

    If you want to combine fields, you could refer to theDBguy's sql.

    If you want to combine rows, I test your current macro and I think it should work for you. Do you get any issue using the macro?

    Best Regards,

    Terry


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Tuesday, November 28, 2017 7:21 AM
    Moderator
  • Public Function MakeTextFile(PList As String) As String
    On Error GoTo MakeTextFile_Err
    Dim db As DAO.Database, rs As DAO.Recordset
    Dim myPartsList As String

        myPartsList = Null
        Set db = CurrentDb
        Set rs = db.OpenRecordset("PartsListQ2", dbOpenSnapshot)

        With rs
            If .RecordCount <> 0 Then
                Do While Not rs.EOF
                    myPartsList = myPartsList & rs(PList)
                    .MoveNext
                Loop
            End If
        End With
                  
        Set rs = Nothing
        Set db = Nothing
       
        Call CreatXML                                               ' Call the function to make the master parts list and export it.
       
    MakeTextFile_Exit:
        Exit Function
    MakeTextFile_Err:
        MsgBox Error$
        Resume MakeTextFile_Exit
    End Function

    Hi Jerry,

    The above Function immediately exits because an error on the assignment of Null to myPartsList (String variable). This variable is already initialized to "".

    Why do you use a Function, if you do not return a function result?

    As you use the db variable only once, you can also directly use CurrentDb to decalre the recordset.

    Testing for .RecordCount <> 0 is not necessary, as this is implicit in NOT rs.EOF.

    You could try:

    Public Function MakeTextFile(PList As String) As String On Error GoTo MakeTextFile_Err

    Dim rs As DAO.Recordset Dim myPartsList As String Set rs = CurrentDb.OpenRecordset("PartsListQ2", dbOpenSnapshot) With rs Do While Not rs.EOF myPartsList = myPartsList & rs(PList) .MoveNext Loop End With Call CreatXML ' Call the function to make the master parts list and export it. MakeTextFile_Exit: Exit Function MakeTextFile_Err: MsgBox Error$ Resume MakeTextFile_Exit End Function


    Imb.

    Tuesday, November 28, 2017 8:15 AM
  • Thanks very much guys. I have been busy trying all of your suggestions listed above and can't get any of them to work. Here is the latest:

    Option Compare Database
    Option Explicit
    Dim myPrtList As String

    Public Function MakeTextFile(myPList As String) As String
    On Error GoTo MakeTextFile_Err
    Dim rs As DAO.Recordset

        Set rs = CurrentDb.OpenRecordset("PartsListQ2", dbOpenSnapshot)
       
        With rs
            Do While Not rs.EOF
            myPList = myPList & .Fields(0)
            .MoveNext
            Loop
        End With
                   
        myPrtList = myPList
       
        Call CreateXML  ' Call the function to make the master parts list and export it.
        
    MakeTextFile_Exit:
         Exit Function
    MakeTextFile_Err:
         MsgBox Error$
         Resume MakeTextFile_Exit
     End Function

    I don't think i am getting a parts list from this or it is not getting passed to the next CreateXML that combines the parts list with other components to make the xml file. When i run the functions i get an Invalid procedure call or argument. I beleive it is because the myPrtList is empty.  Is there a way to see the list that this procedure is creating?  Any ideas?  I have tried:   myPList = myPList & rs(PList) , it says item not found in this collection. PList is the only field in PartsListQ2.

    Tuesday, November 28, 2017 7:49 PM
  • Hi Jerry,

    Just to clarify... Did you say you tried using SimpleCSV()? If so, can you show me exactly how? Thanks.

    Tuesday, November 28, 2017 8:12 PM
  • Here it is:

    'Public Function SimpleCSV(strSQL As String, Optional strDelim As String = ",") As String

    'Returns a comma delimited string of all the records in the SELECT SQL statement
    'Source: http://accessmvp.com/thedbguy
    'v1.0 - 8/20/2013

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strCSV As String

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("PartsListQ2", dbOpenSnapshot)

    'Concatenate the first (and should be the only one) field from the SQL statement
    With rs
        Do While Not .EOF
            strCSV = strCSV & .Fields(0)
            .MoveNext
        Loop
        .Close
    End With

    myPrtList = strCSV

    'Set rs = Nothing
    'Set db = Nothing
    'Call CreateXML                   ' Call the function to make the master parts list and export it.

    'End Function

    • Edited by Jerry215 Tuesday, November 28, 2017 8:29 PM
    Tuesday, November 28, 2017 8:22 PM
  • Hi Jerry,

    Thanks for the clarification. I guess you misunderstood what I suggested. You were supposed to copy the module to your project but there's no need to modify it at all. You simply use it in your own code. For example, your code might change to something like:

    Dim strMyParts As String
    
    strMyParts = SimpleCSV("SELECT Field1 & Field2 FROM PartsListQ2", "")
    
    Call CreateXML

    However, I don't know what CreateXML does, so I am not sure how you're supposed to pass strMyParts to it.

    Hope it makes sense...

    Tuesday, November 28, 2017 8:37 PM
  • I don't think i am getting a parts list from this or it is not getting passed to the next CreateXML that combines the parts list with other components to make the xml file. When i run the functions i get an Invalid procedure call or argument. I beleive it is because the myPrtList is empty.  Is there a way to see the list that this procedure is creating?  Any ideas?  I have tried:   myPList = myPList & rs(PList) , it says item not found in this collection. PList is the only field in PartsListQ2.

    Hi Jerry,

    Did you set a breakpoint in the code, to step line by line through the code and see what happens? The debugger will tell you where and what is wrong.

    Imb.

    Tuesday, November 28, 2017 9:49 PM
  • Ok. I figured out how to use the debugger, been building access db's since 1997 and never used the debugger before. Anyway...i was pulling a parts list from the query but most of it was gerbish. So i went back to DBGuy's code and now i am getting an out of stack space error 28.  I tried setting up the strSQL both ways as shown below and get the same out of stack space error either way. Here is the code as it is being used:

    Option Compare Database
    Option Explicit
    Dim myPrtList As String

    Public Function SimpleCSV(strSQL As String, Optional strDelim As String = ",") As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strCSV As String

    'Returns a comma delimited string of all the records in the SELECT SQL statement
    'Source: http://accessmvp.com/thedbguy
    'v1.0 - 8/20/2013

        strSQL = SimpleCSV("SELECT PList FROM PartsListQ2", "")
        'strSQL = SimpleCSV("SELECT Exp1 & PN & Exp2 & Desc & Exp3 & TQY & Exp4 & Exp5 & Exp6 & Exp7 & Exp8 FROM PartsListQ", "")
       
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

    'Concatenate the first (and should be the only one) field from the SQL statement
        With rs
            Do While Not .EOF
                strCSV = strCSV & strDelim & .Fields(0)
                .MoveNext
            Loop
            .Close
        End With

    'Remove the leading delimiter and return the result
        SimpleCSV = Mid$(strCSV, Len(strDelim) + 1)

        myPrtList = SimpleCSV

        Set rs = Nothing
        Set db = Nothing

    Call CreateXML  ' Call the function to make the master parts list and export it.

    End Function

    Everthing south of here works perfect if i put in a string like this:  myPrtList = "Does this work now"

    Wednesday, November 29, 2017 3:02 PM
  • Hi Jerry,

    I guess I am not doing a very good job explaining how to use the function I provided. You are not supposed to modify it in any way. You simply just use it in your own code.

    So, if you could start over, delete your copy of SimpleCSV() and replace it with the original from my website.

    Then, go back to your original code and simply "call" the SimpleCSV() function, passing a SQL statement to combine all the fields in your table, and the SimpleCSV() function will return a String of Text with all the data from the table.

    You are getting an "out-of-stack" error because you modified my function to become a "recursive" function. In other words, it keeps calling itself without a way out or some way to end.

    If you need help using the function, consider sharing a copy of your db for us to modify.

    Just my 2 cents...

    Wednesday, November 29, 2017 3:39 PM
  • Sorry i didn't understand what i was supposed to do.  I copied and pasted in tact. Called it from the CreateXML function with this:

        myPrtList = SimpleCSV("SELECT PList FROM PartsListQ2", "")

    It works great!

    What can i do to get rid of the commas?

    Thank you very very much!

    Wednesday, November 29, 2017 8:21 PM
  • Sorry i didn't understand what i was supposed to do.  I copied and pasted in tact. Called it from the CreateXML function with this:

        myPrtList = SimpleCSV("SELECT PList FROM PartsListQ2", "")

    It works great!

    What can i do to get rid of the commas?

    Thank you very very much!

    Hi Jerry,

    Glad to hear you made good progress! Hmm, I thought using "" in the second argument should eliminate the commas.

    If you use ";" instead and still see commas, then the commas are coming from somewhere else. If, however, you only see semicolons (;) and the commas were all replaced by semicolons, then I'll have to modify my function.

    Right now, if you don't supply a delimiter, it defaults to using commas. However, that's why we intentionally used "" to explicitly say we don't want commas.

    Does it make sense?

    Wednesday, November 29, 2017 9:33 PM
  • Here's another way to get rid of the commas.

    myPrtList = Replace(SiimpleCSV("SELECT PList FROM PartsListQ2",""),",","")

    Hope it helps...

    Thursday, November 30, 2017 2:57 AM
  • Thanks for all of the help. Sorry it took so long to get back i was very busy yesterday. After further testing it is not working.  When using the debugger window, the code to get the data from the query is working, but not getting all of the information. Here is a screen shot of a 1 part number query from the immediate window:

    Print strCSV
    <PART><PARTNO><![CDATA[1063969             ]]></PARTNO><PARTNAME><![CDATA[ELEMENT AS        ]]></PARTNAME><MODIFIER/><QUANTITY><![CDATA[1]]></QUANTITY><GROUPNO><![CDATA[GroupNOGroupName]]></GROUPNAME><USERNOTE/><PARENTAGE><![CDATA[0]]></PARENTAGE><REFEREN ???U??      ??              ??     ??? ?&  ??? ??????  ?&  ???    ??? ?&  ??? ??  ù   ?&  ??? ?&  ??? ??  ?<PART><PARTNO><![CDATA[1063969             ]]></PARTNO><PARTNAME><![CDATA[ELEMENT AS        ]]></PARTNAME><MODIFIER/><QUANTITY><![CDATA[1]]></QUANTITY><GROUPNO><![CDATA[GroupNOGroupName]]></GROUPNAME><USERNOTE/><PARENTAGE><![CDATA[0]]></PARENTAGE><REFEREN    ?   ?   ?   ??   ?      ??     ??? ?&  ???U      ??      ?       ?   ???     ù   ?&  ??  ?&  ??? ??  

    Then i get an error when it trys to write the list to a text file.


    It should look like this:

    <PART><PARTNO><![CDATA[1063969]]></PARTNO><PARTNAME><![CDATA[ELEMENT AS        ]]></PARTNAME><MODIFIER/><QUANTITY><![CDATA[1]]></QUANTITY><GROUPNO><![CDATA[GroupNOGroupName]]></GROUPNAME><USERNOTE/><PARENTAGE><![CDATA[0]]></PARENTAGE><REFERENCENO><![CDATA[1]]></REFERENCENO><PARTNOTE/><SMCSCODE><![CDATA[]]></SMCSCODE><TYPE><![CDATA[AA]]></TYPE></PART>

    If I make myPrtList = "the above string" everything works perfectly.  Any ideas?

    Thank you. Jerry215

    Friday, December 1, 2017 12:26 PM
  • Hi Jerry,

    Sorry to hear about the set back. At this point, I am not sure I can say where the problem lies without being able to see your database. Are you able to share it with us? You can email it to me, if you like.

    Just my 2 cents...

    Friday, December 1, 2017 7:56 PM
  • Hi Jerry215,

    For resolving your issue, I think you could share us a simple database with your current data and table design, and your expected result, we will try to make a test with provided file.

    Regards,

    Tony


    Help each other

    Monday, December 4, 2017 5:41 AM
  • I sent a copy of my db to theDBguy this morning at:  theDBguy@gmail.com.  I haven't heard anything so i can only assume he got it. 

    Thank you all for all of the help.

    Monday, December 4, 2017 4:54 PM
  • Hi Jerry,

    Got it and sent it back. It took me a little bit of time to see what's going on and come up with a possible solution.

    Hope it helps...

    Monday, December 4, 2017 5:27 PM
  • It is working good. Thank you DBGuy for all the help!

    Jerry215

    • Marked as answer by Jerry215 Tuesday, December 5, 2017 11:35 AM
    Tuesday, December 5, 2017 11:34 AM
  • Hi Jerry,

    You're welcome. Glad we could assist. I am going to do more research to find out if there's a bug in creating recordsets.

    Good luck with your project.

    Tuesday, December 5, 2017 3:56 PM