Loop through rows in table and concatenate values

[deadyankee]

Hi all.

I have a query which generates a table with a single field containing a list of account managers. What I need to do is sequentially read each row in this table and output the values as a concatenated string.

So the table row values:

Account manager 1
Account manager 2
Account manager 3

Becomes:

Account manager 1, Account manager 2, Account manager 3, etc.

Sounds simple! Any suggestions?

 

[AWithers]

One way of doing this is to use a recordset and a loop
something like this

Private sub test()
Dim db As Database
Dim rs As Recordset
Dim yourstring As String

Set db = CurrentDb

Set rs = db.OpenRecordset("yourtable", dbOpenTable)
rs.MoveFirst
Do While rs.EOF = False

'add in code to trap last record and not add comma
yourstring = yourstring + rs!yourfield + ", "
rs.MoveNext
Loop

Set db = Nothing
Set rs = Nothing

end sub

Hope this helps

 

[JoanneM]

Another Way is to do it in the query calling a function


copy the function below and the public variable into the declaration section

Public Prevname


Function mngr(nm As String) As Variant
If Prevname = "" Then
mngr = nm
Prevname = nm
Else
mngr = Prevname & " , " & nm
Prevname = Prevname & " , " & nm
End If

End Function

In your query in one of the columns copy the following

Mngrs: mngr([AccountManagerFieldName])

Replacing AccountManagerFieldName with the actual name of your field.

the result set will give you a list of Mngrs as you require
for each of the mngrs listed

Hope this helps
Regards

Jo