0

I am having a little difficulty witht he syntax of a lookup using access DB

here is the code:

"SELECT * FROM tblContacts WHERE ContactID IN (353,1520,2031); Order by ContactID"

It works find without the order and I have tried without the ; or with the ; at the end, but no luck.

Anyone genious enough to know the correct syntax.

3
Contributors
8
Replies
14
Views
4 Years
Discussion Span
Last Post by pritaeas
0
SELECT * FROM tblContacts WHERE ContactID IN (353,1520,2031) Order by ContactID

Should be the right syntax. Do you get an error? No results? What database?

0

Yes already tried that, error I get is No value given for one or more required parameters.

I am using Access mdb.

0

Im an idiot, I just realised my mistake. But it has not solved my problem, what I now need is the order to be in the order of the IN statement.
For example

SELECT * FROM tblContacts WHERE ContactID IN (1857,2141,2145,2150,1017,1353,3683,3906,3056,3246,3509);

But I want the order of 1857,2141,2145,2150,1017,1353,3683,3906,3056,3246,3509 not numerical. The SQL sorts it automatically, but I dont want it sorted.

0

In the absence of an ORDER BY clause, the query returns the results in the order in which they appear in the database. If you have a field defined as a primary key then that defines the physical order.

0

Yes I know that, but my question is, how do I stop that. I want it in the order of the IN(a,c,b) not a.b.c

0

You can order ascending or descending, or you can take the order that it appears in the database. Or you can retrieve the records into a recordset and loop over the values in the IN clause. You can't force SQL to order them the way you want.

0

You can order ascending or descending, or you can take the order that it appears in the database. Or you can retrieve the records into a recordset and loop over the values in the IN clause. You can't force SQL to order them the way you want.

An overly large UNION could, but I don't recommend it ;)

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.