Where is the ‘IN’ Clause in SQL ?

Topic

New Reply

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….?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

GENIUS
It was the spaces guys, problem solved
THANK YOU VERY MUCH

Reply | Quote

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).

Reply | Quote

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

Reply | Quote