Concatenate Question

[MJ]

I know that this has been asked and answered several times here and I still
seem to be missing a key element.

I am wishing to create a new list based on two fields within a table:

Director RCenter
John Smith 1234
Alan Jones 1235
John Smith 2001

My desired end results would be:

Director RCenter
John Smith 1234, 2001
Alan Jones 1235

I have read Duane Hookom's concatenate and imported the concatenate module.
Unlike Duane's family list example, this data is stored in one table. I am
still getting errors:

Run-time error '-2147217904 (80040e10)':

No value given for one or more required parameters.

My query code is:

SELECT [MyTable].Director, Concatenate("SELECT [MyTable].[RCenter] FROM
[MyTable] WHERE Director =" & [MyTable].[Director]) AS RevCenters
FROM [MyTable];

Duane's code was set up for ADO and had an option for DAO by commenting out
& uncommenting several lines with his code. I guess I am showing my lack of
experience but I don't know the difference. I tried to set it for DAO and
still got errors: " Run-time error '3601': Too few parameters. Expected 1.
"

Can anyone out there help me with this? Thank you in advance.

 

[MJ]

Oh btw, I am using Access 2003 (SP3)

 

[Duane Hookom]

Director is text so you need to treat it like text:

SELECT [MyTable].Director, Concatenate("SELECT [MyTable].[RCenter] FROM
[MyTable] WHERE Director =""" & [MyTable].[Director] & """") AS RevCenters
FROM [MyTable]
GROUP BY
[MyTable].Director, Concatenate("SELECT [MyTable].[RCenter] FROM
[MyTable] WHERE Director =""" & [MyTable].[Director] & """");

--
Duane Hookom
Microsoft Access MVP

Oh btw, I am using Access 2003 (SP3)
--

MJ

I know that this has been asked and answered several times here and I still
seem to be missing a key element.

I am wishing to create a new list based on two fields within a table:

Director RCenter
John Smith 1234
Alan Jones 1235
John Smith 2001

My desired end results would be:

Director RCenter
John Smith 1234, 2001
Alan Jones 1235

I have read Duane Hookom's concatenate and imported the concatenate module.
Unlike Duane's family list example, this data is stored in one table. I am
still getting errors:

Run-time error '-2147217904 (80040e10)':

No value given for one or more required parameters.

My query code is:

SELECT [MyTable].Director, Concatenate("SELECT [MyTable].[RCenter] FROM
[MyTable] WHERE Director =" & [MyTable].[Director]) AS RevCenters
FROM [MyTable];

Duane's code was set up for ADO and had an option for DAO by commenting out
& uncommenting several lines with his code. I guess I am showing my lack of
experience but I don't know the difference. I tried to set it for DAO and
still got errors: " Run-time error '3601': Too few parameters. Expected 1.
"

Can anyone out there help me with this? Thank you in advance.