> Don't you just love security features ;-)
>
> Here's Duane's code module. Be sure to read the comments
> about ADO vs DAO.
> --
> Marsh
> MVP [MS Access]
>
> *************************************************************
> Option Compare Database
>
> Function Concatenate(pstrSQL As String, _
> Optional pstrDelim As String = ", ") _
> As String
> 'Created by Duane Hookom, 2003
> 'this code may be included in any application/mdb providing
> ' this statement is left intact
> 'example
> 'tblFamily with FamID as numeric primary key
> 'tblFamMem with FamID, FirstName, DOB,...
> 'return a comma separated list of FirstNames
> 'for a FamID
> ' John, Mary, Susan
> 'in a Query
> 'SELECT FamID,
> 'Concatenate("SELECT FirstName FROM tblFamMem
> ' WHERE FamID =" & [FamID]) as FirstNames
> 'FROM tblFamily
> '
>
> '======For DAO uncomment next 4 lines=======
> '====== comment out ADO below =======
> 'Dim db As DAO.Database
> 'Dim rs As DAO.Recordset
> 'Set db = CurrentDb
> 'Set rs = db.OpenRecordset(pstrSQL)
>
> '======For ADO uncomment next two lines=====
> '====== comment out DAO above ======
> Dim rs As New ADODB.Recordset
> rs.Open pstrSQL, CurrentProject.Connection, _
> adOpenKeyset, adLockOptimistic
> Dim strConcat As String 'build return string
> With rs
> If Not .EOF Then
> .MoveFirst
> Do While Not .EOF
> strConcat = strConcat & _
> .Fields(0) & pstrDelim
> .MoveNext
> Loop
> End If
> .Close
> End With
> Set rs = Nothing
> '====== uncomment next line for DAO ========
> 'Set db = Nothing
> If Len(strConcat) > 0 Then
> strConcat = Left(strConcat, _
> Len(strConcat) - Len(pstrDelim))
> End If
> Concatenate = strConcat
> End Function
> **************************************************************
>
>
> Nathan-bfhd wrote:
>
> >Thanks for your prompt response. The information you have provided looks
> >promising, unfortunately I'm not able to view the example you provided (yet).
> >I downloaded the example, but when I go to view it, Access gives me a
> >security error saying that the file is located outside my intranet or on an
> >untrusted site. It then tells me to copy it to my machine or local network
> >to open the file. The file is currently on my local machine, so maybe it's
> >trying to access something off of the internet somewhere, I don't know. If
> >you have some bit of code available for me in regard to my original question,
> >or a possible work around of the above mentioned issue - it would be much
> >appreciated.
> >
> >
> >"Marshall Barton" wrote:
> >
> >> Nathan-bfhd wrote:
> >>
> >> >I have tables and queries that track child immunizations at given locations.
> >> >The children are in one table with a unique ID. The Visit information
> >> >(location and date) are in another table with a unique ID. The immunization
> >> >visit consists of looking at a child’s information regarding 17 different
> >> >immunizations and recording the status of them, such as current or needs.
> >> >The immunization info is also in its own table and is linked to the other two
> >> >with the ChildID and the VisitID.
> >> >
> >> >For the report, I need to display all the immunizations needed for each
> >> >child at a specific visit.
> >> >
> >> >I’ve created a query that gives me all the info I need and went ahead and
> >> >attempted the report. I have created a report that groups first by VisitID
> >> >(giving me the location and date), then by ChildID (giving me each individual
> >> >child at that visit) – so far so good. The problem I’m having is in
> >> >displaying the needed immunizations. I have the query set up to only display
> >> >the immunizations marked “Needsâ€, so that’s taken care of, but the way it is
> >> >listing them is not what I need. Since the immunizations are in their own
> >> >table and linked to the children by a ChildID, each immunization is its own
> >> >record. So, I end up with (for example):
> >> >
> >> >FirstName1 LastName1 HepB1 Needs
> >> >FirstName1 LastName1 HepB2 Needs
> >> >FirstName1 LastName1 Hib2 Needs
> >> >FirstName1 LastName1 Hib3 Needs
> >> >
> >> >FirstName2 LastName2 HepB2 Needs
> >> >FirstName2 LastName2 Hib1 Needs
> >> >FirstName2 LastName2 Hib2 Needs
> >> >FirstName2 LastName2 IPV1 Needs
> >> >
> >> >If the child has 10 needed immunizations, I get 10 records with the child’s
> >> >name, immunizations and the status of needs.
> >> >
> >> >Needless to say, this takes up way more space than it needs to and is not
> >> >what I’m looking for. What I want is:
> >> >
> >> >FirstName1 LastName1 HepB1, HepB2, Hib2, Hib3 Needs
> >> >FirstName2 LastName2 HipB2, Hib1, Hib2, IPV1 Needs
> >> >
> >> >I can do without the commas if that is more difficult, and I really don’t
> >> >need the “Needs†status there, but I would like the list of immunizations.
> >>
> >>
> >> You have to create a function to do this kind of thing.
> >> Here's a popular one:
> >>
> >>
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Generic%20Function%20To%20Concatenate%20Child%20Records'> >>
>