Help with faq701-4233 - Concatenate Multiple Child Records

[darall]

I have added the code from faq701-4233 and created a query with the following code:

SELECT tblEngagement_ClarifyData_Subcase.CaseNumber, Concatenate("SELECT [OwnerID] FROM tblEngagement_ClarifyData_Subcase  WHERE [CaseNumber] =" & [CaseNumber],"; ") AS [Case]
FROM tblEngagement_ClarifyData_Subcase;

When I run the query I am getting Run Time Error '3464': Data type mismatch in criteria expression.

When I click on Debug it highlights the following line in the FAQ code:

 Set rs = db.OpenRecordset(pstrSQL)

Help! What am I missing?

 

[MajP]

not totally sure without seeing your code, but it could be that you are assigning a DAO rs to an ADO dimensioned variable. So explicitly identify.

dim db as DAO.database
dim rs as DAO.recordset

set db = currentdb
set rs = db.openrecordset(pstrSql)

If that does not fix it try debugging the function first by hardwiring a case number and see if it returns a good string.

debug.print Concatenate("SELECT [OwnerID] FROM tblEngagement_ClarifyData_Subcase WHERE [CaseNumber] =" 1)

where 1 is a legitimate case number.

 

[JerryKlmns]

SELECT tblEngagement_ClarifyData_Subcase.CaseNumber,
Concatenate("SELECT [OwnerID] FROM tblEngagement_ClarifyData_Subcase WHERE [CaseNumber] =" & [CaseNumber] & ";") AS [Case]
FROM tblEngagement_ClarifyData_Subcase;

 

[PHV]

SELECT tblEngagement_ClarifyData_Subcase.CaseNumber, Concatenate("SELECT [OwnerID] FROM tblEngagement_ClarifyData_Subcase  WHERE [CaseNumber] =[!]'[/!]" & [CaseNumber][!] & "'"[/!],"; ") AS [Case]
FROM tblEngagement_ClarifyData_Subcase;

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

 

[darall]

Thank you PHV, that silly "'" gets me every time! The code works perfectly now.

 

[darall]

I am having another problem with this feature…

My query is working great now, however I have duplicate records due to the one to many relationship.

When I turn on the Totals feature in the query it makes the query run extremely slow. Without the grouping it takes 30 seconds to run, with grouping 6 minutes.

I have tried multiple Group options; group by for all, group by for Case Number/First for Subcase Owners.

Nothing works. I even tried turning it into a make table query to see if I could filter out the duplicates separate from the concatenate feature but this takes even longer, about 10 minutes to run.

Help!

SELECT qryEngagement_ClarifyData_Subcase_ALL.CaseNumber, Concatenate("SELECT [OwnerFull] FROM qryEngagement_ClarifyData_Subcase_ALL  WHERE [CaseNumber] ='" & [CaseNumber] & "'","; ") AS SubcaseOwners
FROM qryEngagement_ClarifyData_Subcase_ALL;