build string by looping through access table

[BillyRogers]

I'm trying to build a string that is composed of values in an access table
field and have them concatenated and separated by commas.

Table Month
May
Jun
July
Aug

ex myString="May,Jun,July,Aug"

How do i loop through the table records and concatenate the field values?


--
Billy Rogers

Dallas,TX

Currently Using Office 2000 and Office 2003

 

[pietlinden]

Try using fConcatChild from Accessweb.
http://www.mvps.org/access/modules/mdl0004.htm

 

[BillyRogers]

Just thought I'd post the solution that I finally settled on in case anyone
has a similar problem. This procedure puts the field contents in single
quotations, separates them by commas, removes the comma after the last item
and then places parenthesis before and after the string.

I plan on using this in the "WHERE fieldName IN " part of a passthrough
query.

Billy



Option Compare Database
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Sub AssocNumList()
Dim concatYear As String
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open "SELECT DISTINCT [Assoc #] FROM [Assoc-Association #]", cnn,
adOpenDynamic, adLockOptimistic

rs.MoveFirst

Do Until rs.EOF = True
fldAssocNum = rs![Assoc #]
rs.MoveNext
concatNum = concatNum & "'" & fldAssocNum & "'" & ","
MsgBox concatNum
Loop

concatNum = Left(concatNum, (Len(concatNum)) - 1)
concatNum = "(" & concatNum & ")"
MsgBox concatNum

cnn.Close

Set cnn = Nothing
Set rs = Nothing
End Sub
--
Billy Rogers

Dallas,TX

Currently Using Office 2000 and Office 2003
& SQL Server 2000