Here's a little function of mine which lists all addressees at an address as
a string along the lines of 'Bob White, John Green, Sarah Brown and Aaron
Black':
Public Function GetAddressees(strAddress As String) As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strAddressees As String
strSQL = "SELECT FirstName & "" "" & LastName " & _
"AS FullName FROM Addresses " & _
"WHERE Address = """ & strAddress & _
""" ORDER BY LastName, FirstName"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
With rst
Do While Not .EOF
strAddressees = strAddressees & ", " & _
.Fields("FullName")
.MoveNext
Loop
.Close
' remove leading comma and space
strAddressees = Mid$(strAddressees, 3)
End With
' replace final comma, if exists, with 'and'
If InStr(strAddressees, ",") > 0 Then
strAddressees = Left(strAddressees, InStrRev(strAddressees, ",") -
1) & _
" and" & Mid(strAddressees, InStrRev(strAddressees, ",") + 1)
End If
GetAddressees = strAddressees
End Function
But as you are doing this in a report then an 'across then down'
multi-column subreport would be an easy solution. You'll find an example at:
http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24271
The file which you can download from that link also includes a means of
doing it as a single report by manipulating the report's layout at runtime by
means of code in its module. That however was only produced to show that it
could be done, in answer to a reader's request to a contact of mine who
writes a databases column for a magazine. It produces exactly the same end
result as the subreport solution, but a subreport is much easier, requiring
no code whatsoever.
Ken Sheridan
Stafford, England
