Concatenate Error

J

jenniferspnc

Please let me apologize in advance for reposting, but I had replied to an
older post in hopes of help but it may have been overlooked. I'm still
struggling and have looked at this so much that I may be missing the obvious.

I used the concatenate function succesfully once; however, I applied the same
concept to another query and it's not working properly.

Here's an example of my tables related to what I'm doing:
Countries (Table)
Country_ID (PK)
Country

Currencies (Table)
Currency_ID (PK)
Currency

tbl_currencybridge (Table)
UniqueID (PK)
Currency_ID
Country_ID

So for my first query (currencytest) I have: SELECT
tbl_currencybridge.Country_ID, Currencies.Currency
FROM Currencies INNER JOIN tbl_currencybridge ON Currencies.Currency_ID =
tbl_currencybridge.Currency_ID;

And then ran the second query (currency concatenate)
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT currency
FROM currencytest WHERE Country_ID =" & [Country_ID]) AS Currency_Offered
FROM Countries;
The above works perfectly.

Now trying to apply the same logic to languages and keep getting a runtime
error.
Languages (Table)
Language_ID (PK)
Language

tbl_Languagebridge (Table)
LanguageUniqueID (PK)
Language_ID
Country_ID

first query (languagetest)
SELECT tbl_languagebridge.Country_ID, Languages.Language
FROM Languages INNER JOIN tbl_languagebridge ON Languages.Language_ID =
tbl_languagebridge.Language_ID;

second query works; however, it shows me the language ID instead of the
Languages.
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" & [Country_ID]) AS
Language_Offered
FROM Countries;

So when I try to substitue the language field
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT language
FROM languagetest WHERE Country_ID =" & [Country_ID]) AS languages_Offered
FROM Countries;

I get the error Method 'Open' of object '_Recordset' failed
and when I hit debug it goes to
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Help is greatly appreciated. Thank you!!
 
S

S.Clark

Is it a case where one (or more) country does NOT have any languages assigned
to it?
 
J

jenniferspnc

Yes, and that is the same for Currency too but only having issues with
Languages.

S.Clark said:
Is it a case where one (or more) country does NOT have any languages assigned
to it?

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

jenniferspnc said:
Please let me apologize in advance for reposting, but I had replied to an
older post in hopes of help but it may have been overlooked. I'm still
struggling and have looked at this so much that I may be missing the obvious.

I used the concatenate function succesfully once; however, I applied the same
concept to another query and it's not working properly.

Here's an example of my tables related to what I'm doing:
Countries (Table)
Country_ID (PK)
Country

Currencies (Table)
Currency_ID (PK)
Currency

tbl_currencybridge (Table)
UniqueID (PK)
Currency_ID
Country_ID

So for my first query (currencytest) I have: SELECT
tbl_currencybridge.Country_ID, Currencies.Currency
FROM Currencies INNER JOIN tbl_currencybridge ON Currencies.Currency_ID =
tbl_currencybridge.Currency_ID;

And then ran the second query (currency concatenate)
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT currency
FROM currencytest WHERE Country_ID =" & [Country_ID]) AS Currency_Offered
FROM Countries;
The above works perfectly.

Now trying to apply the same logic to languages and keep getting a runtime
error.
Languages (Table)
Language_ID (PK)
Language

tbl_Languagebridge (Table)
LanguageUniqueID (PK)
Language_ID
Country_ID

first query (languagetest)
SELECT tbl_languagebridge.Country_ID, Languages.Language
FROM Languages INNER JOIN tbl_languagebridge ON Languages.Language_ID =
tbl_languagebridge.Language_ID;

second query works; however, it shows me the language ID instead of the
Languages.
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" & [Country_ID]) AS
Language_Offered
FROM Countries;

So when I try to substitue the language field
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT language
FROM languagetest WHERE Country_ID =" & [Country_ID]) AS languages_Offered
FROM Countries;

I get the error Method 'Open' of object '_Recordset' failed
and when I hit debug it goes to
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Help is greatly appreciated. Thank you!!
Click to expand...
Click to expand...
 
J

jenniferspnc

I'm hoping of a work around since I've not seen a post to explain why the
concatenate isn't working. So in sticking with this query (below)

SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" & [Country_ID]) AS
Language_Offered FROM Countries;

It puts all the language ID's into one field "Language Offered". Now I need
to identify those languages based on language id, but not sure how to do
this. So the numbers 1, 2, 5, 12 may appear in "Language Offered" but now I
need it to say English, Italian, French, Polish (all in one field).

Suggestions?

Thanks.

S.Clark said:
Is it a case where one (or more) country does NOT have any languages assigned
to it?

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

jenniferspnc said:
Please let me apologize in advance for reposting, but I had replied to an
older post in hopes of help but it may have been overlooked. I'm still
struggling and have looked at this so much that I may be missing the obvious.

I used the concatenate function succesfully once; however, I applied the same
concept to another query and it's not working properly.

Here's an example of my tables related to what I'm doing:
Countries (Table)
Country_ID (PK)
Country

Currencies (Table)
Currency_ID (PK)
Currency

tbl_currencybridge (Table)
UniqueID (PK)
Currency_ID
Country_ID

So for my first query (currencytest) I have: SELECT
tbl_currencybridge.Country_ID, Currencies.Currency
FROM Currencies INNER JOIN tbl_currencybridge ON Currencies.Currency_ID =
tbl_currencybridge.Currency_ID;

And then ran the second query (currency concatenate)
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT currency
FROM currencytest WHERE Country_ID =" & [Country_ID]) AS Currency_Offered
FROM Countries;
The above works perfectly.

Now trying to apply the same logic to languages and keep getting a runtime
error.
Languages (Table)
Language_ID (PK)
Language

tbl_Languagebridge (Table)
LanguageUniqueID (PK)
Language_ID
Country_ID

first query (languagetest)
SELECT tbl_languagebridge.Country_ID, Languages.Language
FROM Languages INNER JOIN tbl_languagebridge ON Languages.Language_ID =
tbl_languagebridge.Language_ID;

second query works; however, it shows me the language ID instead of the
Languages.
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" & [Country_ID]) AS
Language_Offered
FROM Countries;

So when I try to substitue the language field
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT language
FROM languagetest WHERE Country_ID =" & [Country_ID]) AS languages_Offered
FROM Countries;

I get the error Method 'Open' of object '_Recordset' failed
and when I hit debug it goes to
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Help is greatly appreciated. Thank you!!
Click to expand...
Click to expand...
 
J

John Spencer

WHere is the language name spelled out? It is probably in another table that
you need to include in the query statement used by the Concatenate function.
Something like the following where you substitute your table and field names
as appropriate.

Concatenate("SELECT tbl_Languages.LanguageName FROM tbl_languagebridge INNER
JOIN tbl_Languages on tbl_LanguageBridge.Language_ID =
tbl_Languages.Language_ID WHERE Country_ID =" & [Country_ID]


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I'm hoping of a work around since I've not seen a post to explain why the
concatenate isn't working. So in sticking with this query (below)

SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" & [Country_ID]) AS
Language_Offered FROM Countries;

It puts all the language ID's into one field "Language Offered". Now I need
to identify those languages based on language id, but not sure how to do
this. So the numbers 1, 2, 5, 12 may appear in "Language Offered" but now I
need it to say English, Italian, French, Polish (all in one field).

Suggestions?

Thanks.

S.Clark said:
Is it a case where one (or more) country does NOT have any languages assigned
to it?

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

jenniferspnc said:
Please let me apologize in advance for reposting, but I had replied to an
older post in hopes of help but it may have been overlooked. I'm still
struggling and have looked at this so much that I may be missing the obvious.

I used the concatenate function succesfully once; however, I applied the same
concept to another query and it's not working properly.

Here's an example of my tables related to what I'm doing:
Countries (Table)
Country_ID (PK)
Country

Currencies (Table)
Currency_ID (PK)
Currency

tbl_currencybridge (Table)
UniqueID (PK)
Currency_ID
Country_ID

So for my first query (currencytest) I have: SELECT
tbl_currencybridge.Country_ID, Currencies.Currency
FROM Currencies INNER JOIN tbl_currencybridge ON Currencies.Currency_ID =
tbl_currencybridge.Currency_ID;

And then ran the second query (currency concatenate)
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT currency
FROM currencytest WHERE Country_ID =" & [Country_ID]) AS Currency_Offered
FROM Countries;
The above works perfectly.

Now trying to apply the same logic to languages and keep getting a runtime
error.
Languages (Table)
Language_ID (PK)
Language

tbl_Languagebridge (Table)
LanguageUniqueID (PK)
Language_ID
Country_ID

first query (languagetest)
SELECT tbl_languagebridge.Country_ID, Languages.Language
FROM Languages INNER JOIN tbl_languagebridge ON Languages.Language_ID =
tbl_languagebridge.Language_ID;

second query works; however, it shows me the language ID instead of the
Languages.
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" & [Country_ID]) AS
Language_Offered
FROM Countries;

So when I try to substitue the language field
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT language
FROM languagetest WHERE Country_ID =" & [Country_ID]) AS languages_Offered
FROM Countries;

I get the error Method 'Open' of object '_Recordset' failed
and when I hit debug it goes to
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Help is greatly appreciated. Thank you!!
Click to expand...
Click to expand...
Click to expand...
 
J

jenniferspnc

Not trying to be dense here, but I don't get it. Why does this work with
language_id
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" & [Country_ID]) AS
Language_Offered
FROM Countries;

but the below returns an error message (method 'open' of object_recordset'
failed)? Here I'm pulling language in "select language" from the
languagetest query.
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT language
FROM languagetest WHERE Country_ID =" & [Country_ID]) AS Language_Offered
FROM Countries;



John Spencer said:
WHere is the language name spelled out? It is probably in another table that
you need to include in the query statement used by the Concatenate function.
Something like the following where you substitute your table and field names
as appropriate.

Concatenate("SELECT tbl_Languages.LanguageName FROM tbl_languagebridge INNER
JOIN tbl_Languages on tbl_LanguageBridge.Language_ID =
tbl_Languages.Language_ID WHERE Country_ID =" & [Country_ID]


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I'm hoping of a work around since I've not seen a post to explain why the
concatenate isn't working. So in sticking with this query (below)

SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" & [Country_ID]) AS
Language_Offered FROM Countries;

It puts all the language ID's into one field "Language Offered". Now I need
to identify those languages based on language id, but not sure how to do
this. So the numbers 1, 2, 5, 12 may appear in "Language Offered" but now I
need it to say English, Italian, French, Polish (all in one field).

Suggestions?

Thanks.

S.Clark said:
Is it a case where one (or more) country does NOT have any languages assigned
to it?

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

:

Please let me apologize in advance for reposting, but I had replied to an
older post in hopes of help but it may have been overlooked. I'm still
struggling and have looked at this so much that I may be missing the obvious.

I used the concatenate function succesfully once; however, I applied the same
concept to another query and it's not working properly.

Here's an example of my tables related to what I'm doing:
Countries (Table)
Country_ID (PK)
Country

Currencies (Table)
Currency_ID (PK)
Currency

tbl_currencybridge (Table)
UniqueID (PK)
Currency_ID
Country_ID

So for my first query (currencytest) I have: SELECT
tbl_currencybridge.Country_ID, Currencies.Currency
FROM Currencies INNER JOIN tbl_currencybridge ON Currencies.Currency_ID =
tbl_currencybridge.Currency_ID;

And then ran the second query (currency concatenate)
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT currency
FROM currencytest WHERE Country_ID =" & [Country_ID]) AS Currency_Offered
FROM Countries;
The above works perfectly.

Now trying to apply the same logic to languages and keep getting a runtime
error.
Languages (Table)
Language_ID (PK)
Language

tbl_Languagebridge (Table)
LanguageUniqueID (PK)
Language_ID
Country_ID

first query (languagetest)
SELECT tbl_languagebridge.Country_ID, Languages.Language
FROM Languages INNER JOIN tbl_languagebridge ON Languages.Language_ID =
tbl_languagebridge.Language_ID;

second query works; however, it shows me the language ID instead of the
Languages.
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" & [Country_ID]) AS
Language_Offered
FROM Countries;

So when I try to substitue the language field
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT language
FROM languagetest WHERE Country_ID =" & [Country_ID]) AS languages_Offered
FROM Countries;

I get the error Method 'Open' of object '_Recordset' failed
and when I hit debug it goes to
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Help is greatly appreciated. Thank you!!
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Barrows [MVP]

jenniferspnc said:
Not trying to be dense here, but I don't get it. Why does this work
with language_id
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" &
[Country_ID]) AS Language_Offered
FROM Countries;

but the below returns an error message (method 'open' of
object_recordset' failed)? Here I'm pulling language in "select
language" from the languagetest query.
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language FROM languagetest WHERE Country_ID =" & [Country_ID]) AS
Language_Offered FROM Countries;
Click to expand...

In the SQL View of a new Query Builder window, paste in the sql that you
are sending to the Concatenate function and test it. Does it work? If
not, you have your answer. If it works fine, then you will need to debug
the procedure.
 
J

John Spencer

It could be that Language is a reserved word and as such you need to
surround it with square brackets as in [Language]

Or is could be that you have misnamed a field or table in the query.

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

Not trying to be dense here, but I don't get it. Why does this work with
language_id
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" & [Country_ID]) AS
Language_Offered
FROM Countries;

but the below returns an error message (method 'open' of object_recordset'
failed)? Here I'm pulling language in "select language" from the
languagetest query.
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT language
FROM languagetest WHERE Country_ID =" & [Country_ID]) AS Language_Offered
FROM Countries;



John Spencer said:
WHere is the language name spelled out? It is probably in another table that
you need to include in the query statement used by the Concatenate function.
Something like the following where you substitute your table and field names
as appropriate.

Concatenate("SELECT tbl_Languages.LanguageName FROM tbl_languagebridge INNER
JOIN tbl_Languages on tbl_LanguageBridge.Language_ID =
tbl_Languages.Language_ID WHERE Country_ID =" & [Country_ID]


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I'm hoping of a work around since I've not seen a post to explain why the
concatenate isn't working. So in sticking with this query (below)

SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" & [Country_ID]) AS
Language_Offered FROM Countries;

It puts all the language ID's into one field "Language Offered". Now I need
to identify those languages based on language id, but not sure how to do
this. So the numbers 1, 2, 5, 12 may appear in "Language Offered" but now I
need it to say English, Italian, French, Polish (all in one field).

Suggestions?

Thanks.

:

Is it a case where one (or more) country does NOT have any languages assigned
to it?

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

:

Please let me apologize in advance for reposting, but I had replied to an
older post in hopes of help but it may have been overlooked. I'm still
struggling and have looked at this so much that I may be missing the obvious.

I used the concatenate function succesfully once; however, I applied the same
concept to another query and it's not working properly.

Here's an example of my tables related to what I'm doing:
Countries (Table)
Country_ID (PK)
Country

Currencies (Table)
Currency_ID (PK)
Currency

tbl_currencybridge (Table)
UniqueID (PK)
Currency_ID
Country_ID

So for my first query (currencytest) I have: SELECT
tbl_currencybridge.Country_ID, Currencies.Currency
FROM Currencies INNER JOIN tbl_currencybridge ON Currencies.Currency_ID =
tbl_currencybridge.Currency_ID;

And then ran the second query (currency concatenate)
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT currency
FROM currencytest WHERE Country_ID =" & [Country_ID]) AS Currency_Offered
FROM Countries;
The above works perfectly.

Now trying to apply the same logic to languages and keep getting a runtime
error.
Languages (Table)
Language_ID (PK)
Language

tbl_Languagebridge (Table)
LanguageUniqueID (PK)
Language_ID
Country_ID

first query (languagetest)
SELECT tbl_languagebridge.Country_ID, Languages.Language
FROM Languages INNER JOIN tbl_languagebridge ON Languages.Language_ID =
tbl_languagebridge.Language_ID;

second query works; however, it shows me the language ID instead of the
Languages.
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" & [Country_ID]) AS
Language_Offered
FROM Countries;

So when I try to substitue the language field
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT language
FROM languagetest WHERE Country_ID =" & [Country_ID]) AS languages_Offered
FROM Countries;

I get the error Method 'Open' of object '_Recordset' failed
and when I hit debug it goes to
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Help is greatly appreciated. Thank you!!
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Barrows [MVP]

Bob said:
jenniferspnc said:
Not trying to be dense here, but I don't get it. Why does this work
with language_id
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" &
[Country_ID]) AS Language_Offered
FROM Countries;

but the below returns an error message (method 'open' of
object_recordset' failed)? Here I'm pulling language in "select
language" from the languagetest query.
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language FROM languagetest WHERE Country_ID =" & [Country_ID]) AS
Language_Offered FROM Countries;
Click to expand...

In the SQL View of a new Query Builder window, paste in the sql that
you are sending to the Concatenate function and test it. Does it
work? If not, you have your answer. If it works fine, then you will
need to debug the procedure.
Click to expand...
Oh wait. I should have said: substitute various country_id values into
the statement and verify that they work. If they do, you are going to
have to debug the procedure to figure out which country_id is causing
the problem.
 
J

jenniferspnc

So the languagetest query I'm calling works fine; however, that concatenate
is still giving me trouble. When I select debug it points to this

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

Thoughts? Thanks for the patience. And ideally if this won't work, as I
mentioned it pulls language id then is there a way to match it up?

For example,
Language_id may return 1, 2, 5, 12 based on the concatenate function and I
would need it to return English, French, Italian, Polish in another column.
Just examples.

Bob Barrows said:
jenniferspnc said:
Not trying to be dense here, but I don't get it. Why does this work
with language_id
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" &
[Country_ID]) AS Language_Offered
FROM Countries;

but the below returns an error message (method 'open' of
object_recordset' failed)? Here I'm pulling language in "select
language" from the languagetest query.
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language FROM languagetest WHERE Country_ID =" & [Country_ID]) AS
Language_Offered FROM Countries;
Click to expand...

In the SQL View of a new Query Builder window, paste in the sql that you
are sending to the Concatenate function and test it. Does it work? If
not, you have your answer. If it works fine, then you will need to debug
the procedure.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
 
J

jenniferspnc

Thank you, this had been driving me crazy for quite a while.

I tested various country_id's and none returned an error which confirmed the
1st query was right.

Then I put brackets around Language and that solved my issue.

Thanks for the suggestions, helped me narrow down where the problem was and
then you all offered the fix!

John Spencer said:
It could be that Language is a reserved word and as such you need to
surround it with square brackets as in [Language]

Or is could be that you have misnamed a field or table in the query.

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

Not trying to be dense here, but I don't get it. Why does this work with
language_id
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" & [Country_ID]) AS
Language_Offered
FROM Countries;

but the below returns an error message (method 'open' of object_recordset'
failed)? Here I'm pulling language in "select language" from the
languagetest query.
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT language
FROM languagetest WHERE Country_ID =" & [Country_ID]) AS Language_Offered
FROM Countries;



John Spencer said:
WHere is the language name spelled out? It is probably in another table that
you need to include in the query statement used by the Concatenate function.
Something like the following where you substitute your table and field names
as appropriate.

Concatenate("SELECT tbl_Languages.LanguageName FROM tbl_languagebridge INNER
JOIN tbl_Languages on tbl_LanguageBridge.Language_ID =
tbl_Languages.Language_ID WHERE Country_ID =" & [Country_ID]


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

jenniferspnc wrote:
I'm hoping of a work around since I've not seen a post to explain why the
concatenate isn't working. So in sticking with this query (below)

SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" & [Country_ID]) AS
Language_Offered FROM Countries;

It puts all the language ID's into one field "Language Offered". Now I need
to identify those languages based on language id, but not sure how to do
this. So the numbers 1, 2, 5, 12 may appear in "Language Offered" but now I
need it to say English, Italian, French, Polish (all in one field).

Suggestions?

Thanks.

:

Is it a case where one (or more) country does NOT have any languages assigned
to it?

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

:

Please let me apologize in advance for reposting, but I had replied to an
older post in hopes of help but it may have been overlooked. I'm still
struggling and have looked at this so much that I may be missing the obvious.

I used the concatenate function succesfully once; however, I applied the same
concept to another query and it's not working properly.

Here's an example of my tables related to what I'm doing:
Countries (Table)
Country_ID (PK)
Country

Currencies (Table)
Currency_ID (PK)
Currency

tbl_currencybridge (Table)
UniqueID (PK)
Currency_ID
Country_ID

So for my first query (currencytest) I have: SELECT
tbl_currencybridge.Country_ID, Currencies.Currency
FROM Currencies INNER JOIN tbl_currencybridge ON Currencies.Currency_ID =
tbl_currencybridge.Currency_ID;

And then ran the second query (currency concatenate)
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT currency
FROM currencytest WHERE Country_ID =" & [Country_ID]) AS Currency_Offered
FROM Countries;
The above works perfectly.

Now trying to apply the same logic to languages and keep getting a runtime
error.
Languages (Table)
Language_ID (PK)
Language

tbl_Languagebridge (Table)
LanguageUniqueID (PK)
Language_ID
Country_ID

first query (languagetest)
SELECT tbl_languagebridge.Country_ID, Languages.Language
FROM Languages INNER JOIN tbl_languagebridge ON Languages.Language_ID =
tbl_languagebridge.Language_ID;

second query works; however, it shows me the language ID instead of the
Languages.
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT
language_id FROM tbl_languagebridge WHERE Country_ID =" & [Country_ID]) AS
Language_Offered
FROM Countries;

So when I try to substitue the language field
SELECT Countries.Country_ID, Countries.Country, Concatenate("SELECT language
FROM languagetest WHERE Country_ID =" & [Country_ID]) AS languages_Offered
FROM Countries;

I get the error Method 'Open' of object '_Recordset' failed
and when I hit debug it goes to
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Help is greatly appreciated. Thank you!!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Record with multiple lines 1
Concatenate - Error 3
Concatenate Error: It's just blank! 1
Error Message trying to concatenate from multiple records to a field 9
Query error: Undefined function 'Concatenate' in expression 3
Why there is an error in my Sub in VBA 1
FWT Newsletter - Weekly - February 2, 2004 1
FWT Newsletter - Weekly - October 11, 2004 0

Top