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

 

[MajP]

Current you return this
WHERE keycervexam ='" & [keycervexam] & "'"
which would give you
WHERE keycervexam ='3'

I assume you want
WHERE keycervexam = 3

 

[kc112]

I changed the keycervexam to 10 instead of 3 and got DATA MISMATCH error. I troubleshooted the ?pstrSQL statement and received this:

?pstrsql
SELECT cervflex FROM cervorthoexamnormal WHERE keycervexam ='10'

It does the same thing for whatever I make keycervexam for. If I change it to 3...then its :

?pstrsql
SELECT cervflex FROM cervorthoexamnormal WHERE keycervexam ='3'

Does this mean there is something wrong with my relationships? like, keycervexam in the query is a DATA MISMATCH from the keycervexam from my table?

If so, any ideas on how to correctly 'match' them?

 

[PHV]

Replace this:
WHERE keycervexam ='" & [keycervexam] & "'")
with this:
WHERE keycervexam=" & [keycervexam])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kc112]

Thank you..Thank you...THANK YOU ALL!!! It works! It works! Halleujah it works!!! ))))))))


I changed to:
concatenate("SELECT cervflex FROM cervorthoexamnormal WHERE keycervexam =" & [keycervexam] & "")

AND IT WORKS!!!

Thank you so much, I couldn't have done this without your help!

 

[dhookom]

That's why I asked you several days and messages ago is "keycervexam text or numeric". This thread would have been much shorter if the field type would have been known then.

Duane
Hook'D on Access
MS Access MVP