Loop through rows in table

[deadyankee]

Hi all,

I have a query that creates a table with a list of account managers in a single field with one row per account manager. The user creates this table by modifying a query. What I need to do is to take the value from each row and concatenate them into a single string.

Should be simple! Any suggestions?

 

[jimmythegeek]

Like this?

Acct Mgr1, Acct Mgr 2, ... OR
John Adams, Abraham Lincoln, Harry Truman, ....

Change the red appropriately (this is using DAO, if you need an ADO solution, let us know):

============================
Public Function GetAcctMgrString() As String
Dim strTemp As String
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("tableNameHere", dbOpenSnapshot)

With rs
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
strTemp = strTemp & !FieldNameHere & ", "
.MoveNext
Loop
strTemp = Left(strTemp, Len(strTemp) - 2)
GetAcctMgrString = strTemp
.Close
End If
End With
End Function Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[deadyankee]

Jimmy,

Absolutely fantastic! Works like a dream. Thanks very much.