This isn't working for me.
I've tried several ways of doing it, as
well as tweaking the code, all to no avail. I am using Access 2003 if
that matters.
The tables I have now, (trying to mirror Duane's setup):
tblEOBcodes
EOBcode text (no duplicates)
tblEOBtext
EOBcode text
EOBline text
EOBtext text
Here's my SQL:
SELECT tblEOBcodes.EOBcode, Concatenate("SELECT EOBtext FROM tblEOBtext
WHERE tblEOBtext.EOBcode = " & """ & [tblEOBcodes].[EOBcode] & """) AS
EOBtext
FROM tblEOBcodes;
Here's what I get when I run the query:
EOBcode EOBtext
*
0A
0B
0C
0D
0E
etc, etc
All the EOBtext fields are empty.
Here's Duane Hookom's code, with slight mods by me.
Option Explicit
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
Debug.Print .Fields(0)
.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
Where do I go next? It seems like this should be working.
John Spencer said:
Duane Hookom has already written the code. See
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
You could use that concatenate function in your query. Something like
the following
Field: FullDescription: Concatenate("SELECT EOBText FROM EOBCode WHERE
EOBCode =""" & EobCode & """ ORDER BY EobLine", " ")
Full SQL
SELECT DISTINCT EobCode
,Concatenate("SELECT EOBText FROM EOBCode WHERE EOBCode =""" & EobCode &
""" ORDER BY EobLine", " ") as FullDescrip
FROM EOBCode
It might be more efficient to use
SELECT DEob.EobCode
,Concatenate("SELECT EOBText FROM EOBCode WHERE EOBCode =""" &
DEob.EobCode & """ ORDER BY EobLine", " ") as FullDescrip
FROM (SELECT Distinct EOBCode FROM EOBCode) as DEob
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
Newbie poster on these forums!
...and fairly new to Access, so I
appreciate any help that can be offered.
I have the following:
EOBcode table
EOBcode text field
EOBline text field
EOBtext text field
EOBcode contains a 2 position code, (ex., 0A, 0B, 0C, etc).
EOBline contains a sequence number for the EOBcode, (ex., 0, 1, 2,
etc).
EOBtext contains a line of EOB text, (ex., You have met your
deductible.).
I have many EOB codes that consist of multiple lines of text. Here's
an example:
EOBcode EOBcode EOBLine EOBtext
BB 0 BENEFITS HAVE BEEN REDUCED TO THE OUT-OF-NETWORK
BB 1 BENEFIT LEVEL AS NO REFERRAL WAS RECEIVED.
What I need is a table with a single line of text:
EOBcode EOBtext
BB BENEFITS HAVE BEEN REDUCED TO THE OUT-OF-NETWORK
BENEFIT LEVEL.......
I'm thinking I may need VBA to do this, but it's been a long time since
I last used VBA, 1994, I think....
Any help is greatly appreciated!!
Geoff
The