concatenate records in a query help 2

[kc112]

I am trying to concatenate my records from my query to list in one text box with comma seperators on the report. I want it to look like this:

THe patient demonstrated pain upon cervical flexion in the following region(s): right inferior cervical, left superior cervical, etc...

table: cervorthoexamnormal
key: keycervexam
field1: cervflex
I made my query and entered this:

Cervflex: concatenate (“SELECT cervflex FROM cervorthoexamnormal WHERE keycervexam =”&[keycervexam])

BUT I first got: Please enter a parament for "
Now, I am getting SYNTAX ERROR.

But not really sure where I went wrong, can anyone help?

Please and thank you

 

[traingamer]

Do you have a user-defined function concatenate?

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[kc112]

I'm not sure...I followed D.Hookums example at:

http://www.rogersaccesslibrary.com/forum/generic-function-to-concatenate-child-records_topic16.html

Since you said that, I looked behind his example and he does have a series of code for the function. His is under a MODULE folder. I don't have a module folder...where do I copy and paste the code?

 

[kc112]

scratch that...I right clicked and added ' module' and pasted d.hookums code in its entire form.

Now, I am receiving a syntax error and its highlighting CERVFLEX after SELECT in my code. Not sure why since that is the correct field name.

Cervflex: concatenate (“SELECT cervflex FROM cervorthoexamnormal WHERE keycervexam =”&[keycervexam])

 

[dhookom]

I keycervexam text or numeric? Your expression assumes it's numeric. If it's numeric, try:

Cervflex: concatenate ("SELECT cervflex  FROM cervorthoexamnormal WHERE keycervexam ='" & [keycervexam] & "'")

You should be able to test this by opening the debug window (press Ctrl+G) and entering:

?concatenate ("SELECT cervflex  FROM cervorthoexamnormal WHERE keycervexam = [Your Value Here]")

Duane
Hook'D on Access
MS Access MVP

 

[kc112]

Thank you so much for chiming in Duane! I was hoping you would

table: cervorthoexamnormal
key: keycervexam - autonumber
field1: cervflex - text combo box

I replaced mine with:

Cervflex: concatenate ("SELECT cervflex FROM cervorthoexamnormal WHERE keycervexam ='" & [keycervexam] & "'")

and now I received the error:
TYPE MISMATCH and to debug it takes me to:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

in your code.

I'm really above my head here and any advice, expertise, or help is greatly appreciated!!!

 

[dhookom]

Again, please try this assuming you have a keycervexam number of 3. If you don't have a keycervexam number of 3, substitute a legitimate number.

Depending on your references in VBA, you may need to use the DAO code vs the ADO code or set the appropriate reference. Did you try compile your code? This is done in the code window by selecting Debug->Compile...

You should be able to test this by opening the debug window (press Ctrl+G) and entering:

?concatenate ("SELECT cervflex  FROM cervorthoexamnormal WHERE keycervexam = 3")

Duane
Hook'D on Access
MS Access MVP

 

[kc112]

okay...I ran debug-compile with the following code. (I commented out some and uncommented others, I attempted to follow directions per the code using DAO )

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



I received the error:

COMPILE ERROR: USER DEFINED TYPE NOT DEFINED.

and it highlights: Dim db As DAO.Database

(I really thank you for taking the time to help me with this)

 

[dhookom]

You should open Tools->References and scroll down to find and check:
If Access 2007 or higher
Microsoft Office 12 Access data engine
Else
Microsoft DAO...
End If
Then compile again.

Also, you commented out too many lines where it said to comment out only the next 2 lines.

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

Duane
Hook'D on Access
MS Access MVP

 

[kc112]

I have microsoft access 2000.

I uncommented the correct lines per your post above.

I compiled again and am still receiving the same error:

USER DEFINED TYPE NOT DEFINED

and it highlights: db As DAO.Database

 

[dhookom]

Did you:
You should open Tools->References and scroll down to find and check:
If Access 2007 or higher
Microsoft Office 12 Access data engine
Else
Microsoft DAO...
End If
Then compile again.



Duane
Hook'D on Access
MS Access MVP

 

[kc112]

My apologies, I thought you just wanted to know which access version.

I opened tools - references from the code window and checked: Microsoft DAO 3.51 Object Library

Compiled and received: DATA TYPE MISMATCH
and it highlights:
Set rs = db.OpenRecordset(pstrSQL)

 

[dhookom]

Again, try this in the debug window:

?concatenate ("SELECT cervflex  FROM cervorthoexamnormal WHERE keycervexam = 3")

Duane
Hook'D on Access
MS Access MVP

 

[kc112]

I think I messed up somewhere...

I opened database, hit (control + g) and opened debug window. On the bottom is another window titled "immediate." I pasted your code in this window (i made sure I had a key record for #3) and went to DEBUG -> COMPILE and nothing happens! THen compile gets greyed out. In addition, it states COMPILE_DB3. How did I get this db3 and is this okay?

how do I fix it so that it correctly compiles?

 

[dhookom]

If nothing happens when you compile it is a good thing.

After entering the suggested statement into the debug/immediate window, you need to press the enter/return key in the line to run it and view the results. For example

? Date()    [red]<--- press enter key[/red]
 7/14/2011  [red]<--- should see[/red]

Duane
Hook'D on Access
MS Access MVP

 

[kc112]

OMG! That works! but why wont it work in the query then???

when I hit Enter I receive:

?concatenate ("SELECT cervflex FROM cervorthoexamnormal WHERE keycervexam = 3")
left superior cervical, central superior cervical

But, when I go to run my query, I get the error: DATA TYPE MISMATCH
and it hightlights: Set rs = db.OpenRecordset(pstrSQL) in your code.

Thank you so much for your help! I feel like I'm so close, Duane, don't give up on me yet!!

 

[dhookom]

Is it possible that you have a null value in [keycervexam]? If so, you want to filter these out or handle them using NZ().

Duane
Hook'D on Access
MS Access MVP

 

[kc112]

I deleted everything but the following from my table, there are no NULL values:

pkcervorthoexam keycervexam cervflex
17 3 left inferior thoracic
18 3 central superior cervical
19 3 central inferior cervical
15 3 left superior cervical
16 3 central superior cervical

When I enter:
?concatenate ("SELECT cervflex FROM cervorthoexamnormal WHERE keycervexam = 9")
left inferior thoracic, central superior cervical, central inferior cervical, left superior cervical, central superior cervical

again, the error highlights: Set rs = db.OpenRecordset(pstrSQL) in your code when I try to run the query.

What am I doing wrong?

 

[dhookom]

What is the SQL view of your query where you are using the function?

When the code stops, open the debug window and type in:

? pstrSQL

remember to press enter. Trouble-shoot the SQL statement to see what's wrong.

Duane
Hook'D on Access
MS Access MVP

 

[kc112]

SQL:

SELECT cervorthoexamnormal.keycervexam, concatenate("SELECT cervflex FROM cervorthoexamnormal WHERE keycervexam ='" & [keycervexam] & "'") AS Cervflex
FROM [Cervical Exam Findings] INNER JOIN cervorthoexamnormal ON [Cervical Exam Findings].pkcervexam = cervorthoexamnormal.keycervexam;


pkcervexam = the primary key for Cervical exam table
keycervexam = the foriegn key for cervical ortho exam

I have pkcervexam linked via a one-to-many relationship to keycervexam.


I ran the query, got the error, hit control-g, and entered ?pstrsql, hit return....and got nothing.

I went back to my original table, deleted and started over. Entering 3 for keycervexam for all entries.

Went back to the query and now the query runs with no error, but both fields keycervexam and cervflex are both empty in the query.

hit control-g again and entered:
?concatenate ("SELECT cervflex FROM cervorthoexamnormal WHERE keycervexam = 3")
left inferior thoracic, central superior cervical, central inferior cervical, left superior cervical, central superior cervical

and got the correct results.

so why is the query showing empty results?