Order by INSTR, urgent !

M

Mary

MemberID VID
1002 1001
1003 1002
1004 1003
1005 1003
1007 1001
1012 1002

<%
MemberList = "1001, 1003, 1002"
' The arrangement should not be changed....., the memberID and VID is number
format

SQL = "Select * From Member Where VID In (" & MemberList & ")"
SQL = SQL & " ORDER BY INSTR(""" & MemberList & """, VID)"
Set rs = GetMdbRecordset( "Member.mdb", SQL)

While not rs.eof
A = rs("MemberID")
Response.write A
rs.movenext
Wend
A = rs("memberID")
Response.write A
%>

The results come out is
MemberID VID
1007 1001
1002 1001
1005 1003
1004 1003
1012 1002
1003 1002

The MemberID of each VID always sort from the bottom, for example, 1003 get
1005 first, and then 1004....., I try the following but no hope :
SQL = SQL & " ORDER BY INSTR("" & MemberList & "", VID)"
SQL = SQL & " ORDER BY INSTR("" & MemberList & "", "MemberID)"
Also I try to change the MemberID to text format, eg: '1001', '1002', ......
It still "Bottom First Top Last", the result I want is as below :

MemberID VID
1002 1001
1007 1001
1004 1003
1005 1003
1003 1002
1012 1002

Please Help me how to fix it, Thanks a lot !
 
B

Bob Lehmann

Not sure I understand what you're asking, but maybe ...
SQL = "Select * From Member Where VID In (" & MemberList & ")"
SQL = SQL & " ORDER BY INSTR(""" & MemberList & """, VID), MemberID"

Bob Lehmann
 
B

Bob Barrows [MVP]

Mary said:
MemberID VID
1002 1001
1003 1002
1004 1003
1005 1003
1007 1001
1012 1002

<%
MemberList = "1001, 1003, 1002"
' The arrangement should not be changed....., the memberID and VID is
number format

SQL = "Select * From Member Where VID In (" & MemberList & ")"
SQL = SQL & " ORDER BY INSTR(""" & MemberList & """, VID)"
Set rs = GetMdbRecordset( "Member.mdb", SQL)

Bob has given you your immediate solution, but I just wanted to throw in
this caveat: add this record to the table and try your query using
MemberList="1001,1003,1002,100":

MemberId VID
1021 100

To fix this, you need to remove the spaces from MemberList, add commas to
the beginning and end of it in the InStr call, as well as putting commas
around VID:

MemberList = "1001, 1003, 1002, 100"
MemberList = Replace(MemberList," ","")
SQL = "Select * From Member Where VID In (" & MemberList & ")"
SQL = SQL & " ORDER BY INSTR(""" & "," & _
MemberList & ",', ',' & VID &','), MemberID"

If you Response.Write SQL, you should see

Select * From Member Where VID In (1001,1003,1002,100) ORDER BY
INSTR(',1001,1003,1002,100,', ',' & VID & ','), MemberID

You should be able to open your database in Access, create a new query in
Design view, switch to SQL View, paste the above sql statement in and run it
to see if it gives you the results you want.

You should always test your queries in Access before attempting to run them
from a client application such as ASP. This will allow you to catch
performance issues early in the process, as well as getting the benefit of
the more informative error messages that Access displays when you've done
somehthing wrong.

Bob Barrows
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,906
Latest member
SkinfixSkintag

Latest Threads

Top