Concatenating - Q? for D. Hookom

[Valerie]

Duane, your sample DB has been a great help! It took me some time to relate
it to my situation as I am dealing with multiple tables. I am now at a point
of almost having this solved, but I keep getting an error.

What I have accomplished thus far:

I have a table of names, a table of events and a table with attendees/names
of an event. I wrote a query linking these three tables, as I need to show
an event with all attendees concatenated. From there I created a query that
mimics your "Example of First Names in Family" query. My concantenate
function looks as such:

Attendees: Concatenate("SELECT Name FROM qryEstablishAttendees WHERE
SLBEVNT_CRN =" & [SLBEVNT_CRN])

When I run this query I get the 80040e10 error; "No value given for one or
more required parameters".

Now, for fun I tried this function: Attendees: Concatenate("SELECT Name
FROM qryEstablishAttendees WHERE SLBEVNT_CRN = 'A0049'") A0049 being
one event's primary key (SLBEVNT_CRN). It produced results like this:

A0001 Justin, Valerie, Kelly, Shane
A0002 Justin, Valerie, Kelly, Shane
A0049 Justin, Valerie, Kelly, Shane

and so forth. Those names are the correct names of the attendees for event
A0049, so I must have the concatenate function written properly, with the
exception of why the information after WHERE, is not working properly when I
have it written like this: WHERE SLBEVNT_CRN =" & [SLBEVNT_CRN]) which
mimics your example.

Any ideas?

-Valerie





You would need to add the module containing the function to your MDB. You
can import it or copy and paste the code into an existing or new module.

 

[Duane Hookom]

You are very close. You must place delimiters around text values
(SLBEVNT_CRN). Try:

Attendees: Concatenate("SELECT Name FROM qryEstablishAttendees WHERE
SLBEVNT_CRN =""" & [SLBEVNT_CRN] & """")

--
Duane Hookom
MS Access MVP
--

Duane, your sample DB has been a great help! It took me some time to
relate
it to my situation as I am dealing with multiple tables. I am now at a
point
of almost having this solved, but I keep getting an error.

What I have accomplished thus far:

I have a table of names, a table of events and a table with
attendees/names
of an event. I wrote a query linking these three tables, as I need to
show
an event with all attendees concatenated. From there I created a query
that
mimics your "Example of First Names in Family" query. My concantenate
function looks as such:

Attendees: Concatenate("SELECT Name FROM qryEstablishAttendees WHERE
SLBEVNT_CRN =" & [SLBEVNT_CRN])

When I run this query I get the 80040e10 error; "No value given for one or
more required parameters".

Now, for fun I tried this function: Attendees: Concatenate("SELECT Name
FROM qryEstablishAttendees WHERE SLBEVNT_CRN = 'A0049'") A0049
being
one event's primary key (SLBEVNT_CRN). It produced results like this:

A0001 Justin, Valerie, Kelly, Shane
A0002 Justin, Valerie, Kelly, Shane
A0049 Justin, Valerie, Kelly, Shane

and so forth. Those names are the correct names of the attendees for
event
A0049, so I must have the concatenate function written properly, with the
exception of why the information after WHERE, is not working properly when
I
have it written like this: WHERE SLBEVNT_CRN =" & [SLBEVNT_CRN]) which
mimics your example.

Any ideas?

-Valerie





You would need to add the module containing the function to your MDB. You
can import it or copy and paste the code into an existing or new module.

--
Duane Hookom
MS Access MVP
--

Hi everyone, thanks for the help. DUANE, I tried your example and typed
in
the Concatenate function but received an error message of "Undefined
function
'Concatenate' in expression", when I clicked on help for a description it
stated:

You entered an SQL expression that includes a Function procedure name
that
cannot be recognized. Make sure the function exists, that it can be used
in
SQL expressions, or check the expression to make sure you entered the
name
correctly.

It seems like it's not recognizing 'Concatenate'?????

 

[Valerie]

DUANE, you are the best!!!!! Thanks so much for your assistance, I'm all
set!!!

-Valerie

You are very close. You must place delimiters around text values
(SLBEVNT_CRN). Try:

Attendees: Concatenate("SELECT Name FROM qryEstablishAttendees WHERE
SLBEVNT_CRN =""" & [SLBEVNT_CRN] & """")

--
Duane Hookom
MS Access MVP
--

Duane, your sample DB has been a great help! It took me some time to
relate
it to my situation as I am dealing with multiple tables. I am now at a
point
of almost having this solved, but I keep getting an error.

What I have accomplished thus far:

I have a table of names, a table of events and a table with
attendees/names
of an event. I wrote a query linking these three tables, as I need to
show
an event with all attendees concatenated. From there I created a query
that
mimics your "Example of First Names in Family" query. My concantenate
function looks as such:

Attendees: Concatenate("SELECT Name FROM qryEstablishAttendees WHERE
SLBEVNT_CRN =" & [SLBEVNT_CRN])

When I run this query I get the 80040e10 error; "No value given for one or
more required parameters".

Now, for fun I tried this function: Attendees: Concatenate("SELECT Name
FROM qryEstablishAttendees WHERE SLBEVNT_CRN = 'A0049'") A0049
being
one event's primary key (SLBEVNT_CRN). It produced results like this:

A0001 Justin, Valerie, Kelly, Shane
A0002 Justin, Valerie, Kelly, Shane
A0049 Justin, Valerie, Kelly, Shane

and so forth. Those names are the correct names of the attendees for
event
A0049, so I must have the concatenate function written properly, with the
exception of why the information after WHERE, is not working properly when
I
have it written like this: WHERE SLBEVNT_CRN =" & [SLBEVNT_CRN]) which
mimics your example.

Any ideas?

-Valerie





You would need to add the module containing the function to your MDB. You
can import it or copy and paste the code into an existing or new module.

--
Duane Hookom
MS Access MVP
--

Hi everyone, thanks for the help. DUANE, I tried your example and typed
in
the Concatenate function but received an error message of "Undefined
function
'Concatenate' in expression", when I clicked on help for a description it
stated:

You entered an SQL expression that includes a Function procedure name
that
cannot be recognized. Make sure the function exists, that it can be used
in
SQL expressions, or check the expression to make sure you entered the
name
correctly.

It seems like it's not recognizing 'Concatenate'?????



:

or, the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. The
link
has a sample database you can run to see how to use the function.

--
Duane Hookom
MS Access MVP
--

Take a look at http://www.mvps.org/access/modules/mdl0004.htm or
http://www.mvps.org/access/modules/mdl0008.htm at "The Access Web"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I think this is a pretty easy solution, just don't know it. I have a
report
I'd like to pull from a query that I built from multiple tables. In
each
field the information is the same (from one table), with the
exception
of
one
field of changing information (from another table). Because I'm bad
at
explaining, I'll give a visual. For example, below is what is
displaying
in
the query now:

Date Time Location Attendee
6/6/06 10am USA JR Schwartz
6/6/06 10am USA Allie Snow
6/6/06 10am USA Kelly Cay

I want the report to look like this however (below), and I know
without
some
adjusting somewhere, the report will just print out what I have in
the
query
above repeating the date, time and location:

Date Time Location Attendee
6/6/06 10am USA JR Schwartz, Allie Snow, Kelly
Cay

I belive that I need to adjust something in a second query before I
build
a
report, but I'm just not sure what to do. Any help is greatly
appreciated.

Thank you!
-Valerie