get results from query and put them into one line of text

[megatronixs]

Hi all,

I found some code on the web that almost does the trick I need. I only got lost in how to get all the results from a query that now comes in a few lines into one line, separated by a coma.
this is the results I get now (in the immediate window)
HK AE 18
HK CN 1
HK TR 1
HK VC 1

It would be great if I could get it like this:
HK-AE 18, HK-CN 1, HK-TR 1, HK-VC 1

This is the code I use now:

Public Sub btn_test_Click()
Dim qry_profile_1_scr2_results As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("qry_profile_1_scr2_results")
' print column names
'Dim i As Integer
'For i = 0 To rs.Fields.Count - 2
'    Debug.Print rs(i).Name & vbTab; 'print col names separated with a tab one from each other
'Next i
'Debug.Print rs(rs.Fields.Count - 1).Name 'last one without ; so it adds the newline
Do While Not rs.EOF
    For i = 0 To rs.Fields.Count - 2
        Debug.Print rs(i) & vbTab; 'print values separated with a tab one from each other
    Next i
    Debug.Print rs(rs.Fields.Count - 1) 'last one without ; so it adds the newline
    rs.MoveNext
Loop
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
End Sub

Greetings.

 

[Ranman256]

something like:

Public Sub btn_test_Click()
Dim rs As DAO.Recordset
dim vLine

Set rs = CurrentDb.OpenRecordset("qry_profile_1_scr2_results")
' print column names
    'Dim i As Integer
    'For i = 0 To rs.Fields.Count - 2
    '    Debug.Print rs(i).Name & vbTab; 'print col names separated with a tab one from each other
    'Next i
    'Debug.Print rs(rs.Fields.Count - 1).Name 'last one without ; so it adds the newline

With rs
    Do While Not .EOF
        For i = 0 To .Fields.Count - 2
            'Debug.Print rs(i) & vbTab; 'print values separated with a tab one from each other
            vLine = vLine & .Fields(0).Value & ", "
        Next i
        'Debug.Print (rs.Fields.Count - 1) 'last one without ; so it adds the newline
        .MoveNext
        Debug.Print vLine
        vLine = ""
    Loop
    .Close 'Close the recordset
End With
Set rs = Nothing 'Clean up
End Sub

 

[megatronixs]

Hi Ranman256,

I get now like this:
HK, HK,
HK, HK,
HK, HK,
HK, HK,

to get it like this will be hard: HK-AE 18, HK-CN 1, HK-TR 1, HK-VC 1

Greetings.

 

[megatronixs]

OK, I got closer:

Public Sub btn_test_Click()
Dim qry_profile_1_scr2_results As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("qry_profile_1_scr2_results")
' print column names
'Dim i As Integer
'For i = 0 To rs.Fields.Count - 2
'    Debug.Print rs(i).Name & vbTab; 'print col names separated with a tab one from each other
'Next i
'Debug.Print rs(rs.Fields.Count - 1).Name 'last one without ; so it adds the newline
Do While Not rs.EOF
    For i = 0 To rs.Fields.Count - 2
        Debug.Print rs(i) & "-"; 'print values separated with a tab one from each other
    Next i
    Debug.Print rs(rs.Fields.Count - 1) & ", " & ""; 'last one without ; so it adds the newline
    rs.MoveNext
Loop
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
End Sub

This gives me now:
HK-AE-18, HK-CN-1, HK-TR-1, HK-VC-1,

the last "," would be nice to have it gone
and have the "-" before the number gone would be nice too to get this:
HK-AE 18, HK-CN 1, HK-TR 1, HK-VC 1

Greetings.

 

[megatronixs]

geez, I forgot how can I combine all the above to get it into a variable that I can later use? now I will get results by results but not as full string.

Greetings.

 

[Minty]

I think yo are over complicating this, make a simple query to concatenate the string per record as a calculated field
[HKField] & "-" & [AEField] & " " & [NoFIeld].

Save it. Then use Allen Browns tried and trusted ConcatedRelated function on that query. http://allenbrowne.com/func-concat.html

 

[megatronixs]

Hi Minty,

I took your advice and mixed it with my code to get exactly what I needed
I used this part first: [HKField] & "-" & [AEField] in the query.
Then I did run the below code:

Public Sub btn_test_Click()
Dim qry_profile_1_scr2_results  As String
Dim rs                          As DAO.Recordset
Dim DasResult                   As String
Dim i                           As Integer
Dim s                           As String
Set rs = CurrentDb.OpenRecordset("qry_profile_1_scr2_results")
Do While Not rs.EOF
    For i = 0 To rs.Fields.Count - 1
        s = s & rs(i) & "("
        s = Left(s, Len(s) - 0)
    Next i
    s = Left(s, Len(s) - 1) & ")" & ", "
        rs.MoveNext
Loop
Debug.Print s
DasResult = s
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
End Sub

And I got the below results:
HK-AE(18), HK-CN(1), HK-TR(1), HK-VC(1),

at the end this was more like they wanted to see.

Greetings.