Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Where is the 'IN' Clause in SQL ?

    I need to write a query where the results set should have a field that is one of a number of values ie :
    'IN ("monday","tuesday","friday")'
    but Access appears to only return rows where the field is "monday" - "tuesday" and "friday" are ignored.

    What is going on ? How do I get past this ?
    I'm going nuts and probably missing something obvious....?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where is the 'IN' Clause in SQL ?

    In the Query Design View, try using the OR operator. In the column for your fieldname type "Monday" Or "Tuesday" Or "Friday" in the criteria row.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where is the 'IN' Clause in SQL ?

    I am not in design view, the query is running in vb so is getting the in('values') as a comma seperated string. I need the IN clause. <img src=/S/mad.gif border=0 alt=mad width=15 height=15>

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where is the 'IN' Clause in SQL ?

    Sorry, I misunderstood. I did a test with the code:
    SELECT * FROM YourTable WHERE ((YourTable.[YourField]) In ("Monday","Tuesday","Friday"));
    and it returned records with any of the three values.
    Hope this is more helpful.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Where is the 'IN' Clause in SQL ?

    Are you sure you have records that contain the other two values? I've never had any problems with the In operator.
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where is the 'IN' Clause in SQL ?

    Ok this is where i realise that i havent put up the whole problem. The comparison with the IN clause is in fact below:
    SELECT (lots of fields)
    FROM LocalCR_Vacancy
    WHERE (((Left([LocalCR_Vacancy].[Area_Ref],2)) In ("c1"," c2"," c3"," d1"," d2"," d3"," d4"))
    AND ((Left([LocalCR_Vacancy].[Occ_band],1)) In ("A"," B"," C"," D"," E"," F"," G"," H"," M"," N"," O"," R"," S"," T"," X"," Y")))
    ORDER BY LocalCR_Vacancy.Area_Ref, LocalCR_Vacancy.Description;
    and this doesnt return anymore than matches with the first value in each IN clause when there should be 600+ rows returned from the table

  7. #7
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where is the 'IN' Clause in SQL ?

    I got errors with the double quotes...have you tried it with single quotes?

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where is the 'IN' Clause in SQL ?

    I notice that the "A" and "c1" in your code have no preceeding space, but the rest of the items do (as " B", " c2") Might that be the cause of your code only selecting the first value in each IN clause?

  9. #9
    DaveShmave
    Guest

    Re: Where is the 'IN' Clause in SQL ?

    Tom's right, there should be no spaces with the exception of the space between In and the first parentheses. Therefore, you want: In ("A","B","C") and In ("c1","c2","c3") respectively. It doesn't matter if you have single or double quotes, both should work.

    HTH

    Dave

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where is the 'IN' Clause in SQL ?

    To save some typing you can use
    BETWEEN "A" AND "Z"
    for the second part.
    (On my system, Access treats that as case sensitive - maybe you need to check the case sensitivity for the IN clauses as well).

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where is the 'IN' Clause in SQL ?

    Thanks for the between thoughts.
    The query posted is to recover all rows, when running in anger the requirements may be a,f,i,p... (ie: completely random) so i dont think i can gain anything there.
    The results are all upper case so i dont think the query should miss the rows - i will try it out though.
    thanks

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where is the 'IN' Clause in SQL ?

    GENIUS <img src=/S/joy.gif border=0 alt=joy width=23 height=23> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21> <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    It was the spaces guys, problem solved
    THANK YOU VERY MUCH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •