Yes another concatenate thread

[filthepitome]

I have spent the last hour reading through threads and trying different things and I can't get this to work

Here is the code as it is in the module I created (taken from the FAQ

http://www.tek-tips.com/faqs.cfm?fid=4233

----------------------------------------------------------
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'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
------------------------------------------------------------

Here is my query, which is pulling from a table with three fields.

SELECT Patients_QualifiedReportsCount.[Patient Id], Concatenate("SELECT Report FROM Patients_QualifiedReportsCount
WHERE Patient id =" & [Patient id]) AS AllReports
FROM Patients_QualifiedReportsCount;


When I run the query I get an error and when i click on debug, this is the section highlighted in yellow

Set rs = db.OpenRecordset(pstrSQL)

Let me also add I know no VB, and this is my first attempt at a module/custom function.

TIA

 

[lespaul]

what's the error message?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[SBerthold]

Right at first spot it looks like you are missing the closing quotations and parentheses in:

Concatenate("SELECT Report FROM Patients_QualifiedReportsCount")

 

[filthepitome]

The error message is


Run Time Error '3075':

Syntax error (missing operator) in query expression "Patient id=a patient id value from the table

 

[filthepitome]

Oh and I now realize that I don't need the reference to a second table, so my syntax now looks like this

SELECT Patients_QualifiedReportsCount.[Patient Id], Concatenate("SELECT Report FROM Patients_QualifiedReportsCount") AS AllReports
FROM Patients_QualifiedReportsCount
GROUP BY Patients_QualifiedReportsCount.[Patient Id]
ORDER BY Patients_QualifiedReportsCount.[Patient Id];

and I get close to what I need, but it is concatenating every value in the "report" field for every single record. Or is this how this function is designed to work? I was hoping to have results like

Patient ID AllReports
12345 Report 1, Report 3, Report 7
44556 Report 4, Report 10

So showing just the reports that each person has a record for.

 

[lespaul]

First off, this function is designed to concatenate the many side of a one to many relationship. The one side would contain the PatientID and all the information about the reports would be in another table:

Patients (One patient)
PatientID
OtherPatientInfo


Reports (has many reports)
PatientID
ReportName

Can you try to run this on your TABLES where the relationships exists instead of this query? OR join into back into the reports table.

Leslie

 

[dhookom]

First, since your field name contains a space (a no-no) you must place []s around it. Then if [Patient ID] is text, you must place quotes around the field value.

'if field is numeric
 Concatenate("SELECT Report FROM Patients_QualifiedReportsCount
WHERE [Patient id] = " & [Patient id]) AS AllReports

'if field is text
 Concatenate("SELECT Report FROM Patients_QualifiedReportsCount
WHERE [Patient id] = """ & [Patient id] & """") AS AllReports

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[filthepitome]

dhookom.............I love you Thank you VERY VERY much though, the second code you posted worked perfectly. I do not know much, but I do know spaces are bad, unfortunately I am using a data extract from another DB and I did not rename any fields.

If you have time and are willing, can you break down the string you posted? I am not understanding the WHERE condition, at least how it is comparing the patient ID.

Thank you again.

 

[dhookom]

blush...
Inside the Concatenate function is a basic SQL statement. The SQL selects a single field/expression from a table or query. In order for the SQL to not return the field values from every record, the SQL must provide some type of where clause.
If the where clause didn't vary, we could use something like:

Concatenate("SELECT Report FROM Patients_QualifiedReportsCount
WHERE [Patient id] = ""Abc123"" ") AS AllReports

or

Concatenate("SELECT Report FROM Patients_QualifiedReportsCount
WHERE [Patient id] = 'Abc123' ") AS AllReports

Note that either a single ' or double "" are used since your field value is text.

Since a hard-coded value isn't very usefull, we want to substitute the value from the orginal query. Since we are building an entire SQL string, we need to make sure the single or double quotes are included.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]