Concatenate Records from Record Set (rs) SQL Query from ACCESS Database

[bemp87]

Hi Cummunity,

Needing some help to concatenate row values being retrieved from ACCESS Database. INstead of writing to multiple rows, i need to concatenate all the record set values and write it to a single cell.

Here is a view of part of my code: Currently there are two rows in the database that meet this condition. This would write the first record to TargetRange + 1 which TargetRange = K44. The second row would then be written to K45 + 1. I need to either loop through the records or concatenate them to write the records to a single cell / row. Any guidance?

' QUERY FOR (PENDING VACATION)

 rs31.Open "SELECT Request_Date FROM Vacation WHERE EID ='" & Worksheets("Sched").Range("N17") & "' And Request_Status = 0", cn, , , adCmdText


    
          ' Write the field names (PENDING VACATION)
For intColIndex = 0 To rs31.Fields.Count - 1
TargetRange31.Offset(1, intColIndex).Value = rs31.Fields(intColIndex).Name
    Next


          ' Write recordset (PENDING VACATION)
TargetRange31.Offset(1, 0).CopyFromRecordset rs31

 

[John_w]

Replace the CopyFromRecordSet line with:

    Dim records As Variant, allRecords As String, i As Long
    records = rs31.GetRows
    allRecords = ""
    For i = 0 To UBound(records, 2)
        allRecords = allRecords & records(0, i) & ","
    Next
    TargetRange31.Offset(2, 0).Value = Left(allRecords, Len(allRecords) - 1)

 

[bemp87]

Hi John - is there a way to format this in a way where there is a space in between each record? i.e. "record & " " record " ""

 

[John_w]

Yes - just change the "," to " ".

 

[bemp87]

Thanks John! This worked like a charm. I have one more question. I've slightly altered the query that now pulls (2) columns "Holiday_Name and Holiday_Date". I'm looking to figure out how do I alter the code you provided above to concatenate now what is two column values being retrieved in one record set. I.e. I have rs31 retrieving Holiday_Name and Holiday_Date, and I want it to somehow concatenate it as Holiday_Name & " - " & Holiday_Date both being placed in cell K45. Any guidance here?

 

[John_w]

ADODB RecordSet.GetRows returns a 2-dimensional array of records and fields - records(field index, record index) in my code - so it is just a matter of looping through the record and field indexes. Your first query returned only 1 field, so the field index of the array is always 0, i.e. records(0, i). For multiple fields we also loop through the field indexes:

    Dim records As Variant, allRecords As String
    Dim r As Long, f As Long
    
    records = rs31.GetRows
    allRecords = ""
    For r = 0 To UBound(records, 2)
        For f = 0 To UBound(records, 1)
            allRecords = allRecords & records(f, r) & " - "
        Next
        allRecords = Left(allRecords, Len(allRecords) - 3)
        allRecords = allRecords & ", "
    Next
    TargetRange31.Offset(1, 0).Value = Left(allRecords, Len(allRecords) - 2)

in the above code each record is separated by ", ".