Hi Sarah
Try this function:
Public Function DelimitedList( _
RecordSource As String, _
Optional ListField As Variant = 0, _
Optional Delimiter As String = ", ") As String
Dim db As dao.Database, rs As dao.Recordset, sList As String
On Error GoTo ProcErr
Set db = CurrentDb()
Set rs = db.OpenRecordset(RecordSource, dbOpenSnapshot, dbForwardOnly)
Do Until rs.EOF
sList = sList & rs(ListField) & Delimiter
rs.MoveNext
Loop
If Len(sList) Then sList = Left(sList, Len(sList) - Len(Delimiter))
ProcEnd:
On Error Resume Next
rs.Close
DelimitedList = sList
Exit Function
ProcErr:
MsgBox Err.Description, vbExclamation
Resume ProcEnd
End Function
Using Northwind's Employees table as an example, you can call it like this:
?DelimitedList("Employees", "City")
or, to sort the list, use a SQL statement:
?DelimitedList("Select City from Employees order by City")
or to eliminate duplicates, use SELECT DISTINCT:
?DelimitedList("Select distinct City from Employees")
