Syntax. Nested select returns contents of an "in clause".

[canugi]

I need to store the contents of an SQL "in clause" in an MS Access 2000 table.
I use MS Access version 9.0.8960 (SP3)

This is my statement (and it works fine with the explicit "in clause" parenthesis):

SELECT Permutation FROM PermutationsOne WHERE 
Permutation in ('B01','B02','B06');

Now, I want to store different strings similar to 'B01','B02','B0 6' in table beentogether. Both fields beentogetherwit h and member of table beentogether are TEXT type.

My statement will now look like this (***):

SELECT Permutation FROM PermutationsOne WHERE 
Permutation  in (select beentogetherwith from beentogether where member = 'B05');

Statement "select beentogetherwit h from beentogether where member = 'B05'"
when executed alone returns:

'B01','B02','B0 6'

My problem is, HOW DO I STORE 'B01','B02','B0 6' in table beentogetherwit h? (Field beentogetherwit h = TEXT)

I have succeeded only with one trivial case. If I store only one value:

B06

just like this with no apostrophes or quotes then statement (***) works fine. The field is TEXT type. Though, I need to store more values in each string, just like in the first example: 'B01','B02','B0 6'.

These are the strings I have stored. NONE OF THEM work:

B02 B06 (no quotes no comma)
'B02 B06' (outer quotes no comma)
B02, B06 (no quotes comma)
'B02, B06' (outer quotes comma)
'B06' (single quotes)
\'B06\' (back slash escaped single quotes)
\\'B06\\' (twice back slash escaped single quotes)

''B06'' (double single quotes)
\''B06\'' (back slash escaped double single quotes)
\\''B06\\'' (twice back slash escaped double single quotes)

"B06" (double quotes)
\\"B06\\" (twice back slash escaped single double quotes)

""B06"" (double double quotes )
\"B06\" (back slash escaped double quotes)
\""B06\"" (back slash escaped double double quotes)
\\""B06\\"" (twice back slash escaped double double quotes)

Storing B06 alone with no quotes or apostrophes statement (***) WORKS FINE!!
I need to store a number of strings that will be returned by the select statement to conform the contents of the "in clause" 's parenthesis. ideas, anyone?

Thank you for your advise!
<Email removed>

[Atli]

Hi canugi. Welcome to Bytes!

First of all, let me point you in the direction of our Posting Guidelines, which we require that all members follow when posting in the forums.

Those guidelines include the use of [code] tags around code (or in your case; SQL queries) and that email addresses are not allowed in the technical forums.
Both of which I had to correct in your post.

Also, as you are asking a question about MS Access, you would be better of asking it in the Access forum (which you can select from the blue navigation bar at the top). I'm afraid we MySQL experts can do little to help you.
But not to worry, I shall move it across to the Access forum for you.

Just make sure you post any future questions in the correct forum, and that you follow the guidelines when you do.

Thank you.
Moderator