Simple Query help for someone new to Access

[Guest]

I just need a simple query to pull all rows from one field in a table and to
convert the records into a comma separated string. For example:

SELECT ALL [FUND] FROM [FUNDS];

Then convert the results into:

Dim strFundList as string
strFundList = '801', '802', '803', '804'

Please include all code to connect to the current DB and convert the
recordset (?) into the string.

Thanks much in advance.

 

[Guest]

In the time that no one replied I worked the following out, is this the most
efficient method?

Dim rs As DAO.Recordset
Dim strList As String
Set rs = CurrentDb.OpenRecordSet("SELECT * FROM [FUNDS];")
rs.MoveFirst
Do While Not rs.EOF
strList = strList & "'" & rs!FUND & "', "
rs.MoveNext
Loop
strList = Left(strList, Len(strList) - 2)

 

[John Spencer (MVP)]

Don't Know. I do know it is the most efficient method that I know of, but that
doesn't mean someone else does not have a better method. I would suggest you
could make it a little more efficient by only returning the field(s) you need in
your recordset.

"SELECT Fund From Funds" so that less data needs to be pumped into your recordset.

In the time that no one replied I worked the following out, is this the most
efficient method?

Dim rs As DAO.Recordset
Dim strList As String
Set rs = CurrentDb.OpenRecordSet("SELECT * FROM [FUNDS];")
rs.MoveFirst
Do While Not rs.EOF
strList = strList & "'" & rs!FUND & "', "
rs.MoveNext
Loop
strList = Left(strList, Len(strList) - 2)

I just need a simple query to pull all rows from one field in a table and to
convert the records into a comma separated string. For example:

SELECT ALL [FUND] FROM [FUNDS];

Then convert the results into:

Dim strFundList as string
strFundList = '801', '802', '803', '804'

Please include all code to connect to the current DB and convert the
recordset (?) into the string.

Thanks much in advance.