ORDER BY Instr

[gray]

Hi

WinXPPro Sp3
Access 2007

After some research I thought I had found a neat way to SELECT records from a table based on an 'IN' clause and sort them in the same order as the values for the 'IN' clause... i.e.

SELECT Unique_No, Table_Name, List_Order FROM My_Table 
WHERE Table_Name = 'Titles' 
AND List_Order IN (3,1,15,4,5,12,7,2) 
ORDER BY INSTR('3,1,15,4,5,12,7,2', List_Order)

Unfortunately, this returns list_order 5 just after 15 and list_order 2 just after 12, thus

List_Order
3
1
15
5
4
12
2
7

Any idea how I can cure this please?

 

[JHB]

It doesn't work because, if you look after "5", you find it in "15", and the same for "2", it is in "12", therefore your sort order is not as expected.
But if you add a "," before and after the numbers in the ORDER BY string and the same before and after
[List_Order] then it should work.

[FONT=Arial][SIZE=3] [FONT=Arial Black]SELECT Unique_No, Table_Name, List_Order
FROM My_Table
WHERE Table_Name = 'Titles' AND List_Order In (3,1,15,4,5,12,7,2)
ORDER BY InStr('[COLOR=Red][B],[/B][/COLOR]3,1,15,4,5,12,7,2[B][COLOR=Red],[/COLOR][/B]',[COLOR=Red][B]"," &[/B][/COLOR] 
[List_Order] [B][COLOR=Red]& ","[/COLOR][/B]);[/FONT]
[/SIZE][/FONT]

 

[gray]

HI

That's worked a treat thanks!!

I had a feeling I'd need some more commas in there somewhere !

Cheers

 

[JHB]

I had a feeling I'd need some more commas in there somewhere !

You were right, it was only the question, where to put them.