Order by INSTR, urgent !

Discussion in 'ASP General' started by Mary, Jan 22, 2006.

  1. Mary

    Mary Guest

    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 !
    Mary, Jan 22, 2006
    #1
    1. Advertising

  2. Mary

    Bob Lehmann Guest

    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

    "Mary" <> wrote in message
    news:%...
    > 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 !
    >
    >
    Bob Lehmann, Jan 22, 2006
    #2
    1. Advertising

  3. Mary wrote:
    > 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

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Jan 23, 2006
    #3
  4. Mary

    Mary Guest

    Dear Bob :
    It work ! You solve my problem, thanks a lot.....


    "Bob Lehmann" <> ¼¶¼g©ó¶l¥ó·s»D:...
    > 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
    >
    > "Mary" <> wrote in message
    > news:%...
    >> 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 !
    >>
    >>

    >
    >
    Mary, Jan 23, 2006
    #4
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Kathy Burke
    Replies:
    0
    Views:
    403
    Kathy Burke
    Nov 24, 2003
  2. =?Utf-8?B?cm9kY2hhcg==?=

    InStr test

    =?Utf-8?B?cm9kY2hhcg==?=, Nov 7, 2006, in forum: ASP .Net
    Replies:
    12
    Views:
    604
    =?Utf-8?B?cm9kY2hhcg==?=
    Nov 15, 2006
  3. Replies:
    1
    Views:
    3,462
    Victor Bazarov
    Jul 13, 2005
  4. J. Muenchbourg

    InStr, substr, Mid ???

    J. Muenchbourg, Aug 13, 2003, in forum: ASP General
    Replies:
    1
    Views:
    153
  5. Graham Mattingley

    InStr Help

    Graham Mattingley, Nov 10, 2003, in forum: ASP General
    Replies:
    4
    Views:
    137
    Chris Barber
    Nov 10, 2003
Loading...

Share This Page