Creating a List from Field Data

[Helen]

How can I create a text list, separated by commas, of data
in a query field?

I want to automate creating an e-mail distribution group,
using the e-mail address field.

Thanks for your help!

 

[Helen]

Thanks Marshall - this is cool, but what I need is a
little different- there is only one field from a query
that I need for the list. I don't know enough to play
with what's here, and Dev is pretty clear about altering
his code.

Do you have any suggestions?

 

[Rodrigo]

try this.

Just copy this into a module. You'll need to add your error checking.

Look at the test sub to see how to use it.

Rodrigo

sub Test()
dim strEmails as string
strEmails = ParseQryResults("qryEmails","EmailAddress")
msgbox strEmails
end sub

Public function ParseQryResults(QryName_or_SQL_or_TableName as string,
FieldName as string) as string
On error resume next
dim db as dao.database
dim rst as dao.recordset
dim strTmp as string

' Open a recordset with your table/query/sql and make sure it has records
set db = dbengine(0)(0)
set rst = db.openrecordset(QryName_or_SQL_or_TableName)
if (rst.eof and rst.bof) then
exit function
end if

' loop trough the values on the field you want adding them to a
temporary string and adding a comma
while not rst.eof
strtmp = strtmp & rst(fieldname).value & ", "
rst.movenext
wend
set rst = nothing
set db = nothing
' take out the comma and space and return the value of the temporary string
ParseQryResults = left(strtmp,len(strtmp)-2)
end function

 

[Marshall Barton]

Thanks Marshall - this is cool, but what I need is a
little different- there is only one field from a query
that I need for the list. I don't know enough to play
with what's here, and Dev is pretty clear about altering
his code.

Do you have any suggestions?

I hate to suggest that anyone use code they don't
understand. What are you going to do if you need to change
your table and can't figure out how to make a corresponding
change to the code you're using?

Maybe, if you'll explain your table(s?), their fields,
provide some examples of the data and what result you need,
we can put somthing together that will do what you need.
--
Marsh
MVP [MS Access]