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.
Jump to PostSELECT * FROM tblContacts WHERE ContactID IN (353,1520,2031) Order by ContactIDShould be the right syntax. Do you get an error? No results? What database?
Jump to PostIn 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.
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?
Yes already tried that, error I get is No value given for one or more required parameters.
I am using Access mdb.
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.
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.
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
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.
OK Thanks, thats what I needed to know.
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 ;)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.
