By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,131 Members | 1,560 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,131 IT Pros & Developers. It's quick & easy.

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

P: 1
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):

Expand|Select|Wrap|Line Numbers
  1. SELECT Permutation FROM PermutationsOne WHERE 
  2. Permutation in ('B01','B02','B06');
Now, I want to store different strings similar to 'B01','B02','B06' in table beentogether. Both fields beentogetherwith and member of table beentogether are TEXT type.

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

Expand|Select|Wrap|Line Numbers
  1. SELECT Permutation FROM PermutationsOne WHERE 
  2. Permutation  in (select beentogetherwith from beentogether where member = 'B05');
Statement "select beentogetherwith from beentogether where member = 'B05'"
when executed alone returns:

'B01','B02','B06'

My problem is, HOW DO I STORE 'B01','B02','B06' in table beentogetherwith? (Field beentogetherwith = 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','B06'.

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>
Oct 27 '08 #1
Share this Question
Share on Google+
1 Reply


Atli
Expert 5K+
P: 5,058
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
Oct 27 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.