sql Select: Any Way of Shortening OR Statement?

[Gooner]

I am selecting records in an Access 2000 db based on a numeric column called 'id'.

sql = "SELECT * FROM tables WHERE id=1 OR id=2 OR id=3..." etc, etc.

Is there a way of shortening this OR selection? Such as id=1,2,3 (i know this doesn't work)...

Thanks in advance!

 

[KenReay]

Hi

sql = "SELECT * FROM tables WHERE id IN (1,2 ,3..." etc,
etc.

or

sql = SELECT * FROM Tables WHERE Id IN (SELECT Id FROM tblList);"

the later method is good if you have the items yiu want to select in a list (table)



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Gooner]

sql = SELECT * FROM Tables WHERE Id IN (SELECT Id FROM tblList);"

the later method is good if you have the items yiu want to select in a list (table)

I'm guessing that for this statement to work the table needs to be in the same database?

Do you know of anyway to 'select id where id=' the values of a whole dimension in a multi-dimensional array, such as (in vb) "...id IN (" & MyArray,1 & &quot" - (MyArray,1 being the first dimension of the array MyArray).


Cheers for your help mate, this have saved me no end of space and time.

 

[KenReay]

Hi

Yes, table needs to be in same db (or it could be a linked table of course)

No, sorry do not know how to do the multi dimension array, other than wite a loop to build a syting of all values, and include that in the sql you build



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK