CONCATENATE multiple rows

[Laurie]

Hello,

I have multiple rows of schools that are in the same building. For each
building, I want to concatenate each school name and put a pipe (|) between
each one.

I found this link (I was planning on working out the pipe (|) bit later):
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

I tried to copy and convert his names to my names, but I'm getting an error
message: "Undefined function "Concatenate" in expression."

I'm sure that the answer is right in front of me and I am being a bit daft,
but does anyone have a solution for me?

My rows look like this:

Primary_Building_Code Location_Name
K1 School A
K2 School B
K2 School C
M3 School D
M3 School E

I want them to look like this:
Primary_Building_Code Schools in Building
K1 School A
K2 School B | School C
M3 School D | School E

Here's my code:
Schools in Building: Concatenate("SELECT Location_Name FROM 2006-2007
Schools Web Data WHERE Primary_Building_Code = " & [Primary_Building_Code])

Thanks ever so much!

 

[Laurie]

OK, I answered my own question-- I found and imported the module. However,
now I am getting this error message: "Syntax error in FROM clause" and I have
no idea how to fix. When I ask Access to debug, it highlights this part:

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

Any ideas?

Thanks again.

 

[John Spencer]

Names of tables and fields that have spaces must be incapsulated inside
square brackets.

Schools in Building: Concatenate("SELECT Location_Name FROM [2006-2007
Schools Web Data] WHERE Primary_Building_Code = " & Chr(34) &
[Primary_Building_Code] & CHr(34))

Assuming that Primary_Building_Code is a text field it must have quote
marks around it - which the Chr(34) handles.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Laurie]

Beautiful! Thanks, worked like a charm.

Names of tables and fields that have spaces must be incapsulated inside
square brackets.

Schools in Building: Concatenate("SELECT Location_Name FROM [2006-2007
Schools Web Data] WHERE Primary_Building_Code = " & Chr(34) &
[Primary_Building_Code] & CHr(34))

Assuming that Primary_Building_Code is a text field it must have quote
marks around it - which the Chr(34) handles.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hello,

I have multiple rows of schools that are in the same building. For each
building, I want to concatenate each school name and put a pipe (|) between
each one.

I found this link (I was planning on working out the pipe (|) bit later):
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

I tried to copy and convert his names to my names, but I'm getting an error
message: "Undefined function "Concatenate" in expression."

I'm sure that the answer is right in front of me and I am being a bit daft,
but does anyone have a solution for me?

My rows look like this:

Primary_Building_Code Location_Name
K1 School A
K2 School B
K2 School C
M3 School D
M3 School E

I want them to look like this:
Primary_Building_Code Schools in Building
K1 School A
K2 School B | School C
M3 School D | School E

Here's my code:
Schools in Building: Concatenate("SELECT Location_Name FROM 2006-2007
Schools Web Data WHERE Primary_Building_Code = " & [Primary_Building_Code])

Thanks ever so much!