Home > Archive > ASP > November 2005 > Order by array
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| I put the result into an array getting from another db, the arrangement
should not be changed,
MemberList = "'007910', '006853', '007965'"
SQL = "Select MemberID, name From Member"
SQL = SQL & " Where MemberID In (" & MemberList & ")"
Set rs = GetMdbStaticRecordset( "Member.mdb", SQL)
While not rs.eof
........ Wend
But the order changes to 006853 first, then 007910....007965
Is it possible to use "order by" to keep the arrangement
SQL = SQL & " Order By '" & MemberList & "'"
Please help !
| |
| Chris Hohmann 2005-11-14, 6:55 pm |
|
"Peter" <alan@neind.net> wrote in message
news:%230YC7SV6FHA.1188@TK2MSFTNGP12.phx.gbl...
>I put the result into an array getting from another db, the arrangement
>should not be changed,
>
> MemberList = "'007910', '006853', '007965'"
>
> SQL = "Select MemberID, name From Member"
> SQL = SQL & " Where MemberID In (" & MemberList & ")"
>
> Set rs = GetMdbStaticRecordset( "Member.mdb", SQL)
>
> While not rs.eof
> ....... Wend
>
> But the order changes to 006853 first, then 007910....007965
>
> Is it possible to use "order by" to keep the arrangement
>
> SQL = SQL & " Order By '" & MemberList & "'"
>
> Please help !
>
SQL = SQL & "ORDER BY INSTR('" & MemberList & "',MemberID)"
| |
|
| Thanks for yr help, but when I apply yr syntax,
SQL = SQL & "ORDER BY INSTR('" & MemberList & "',MemberID)"
the result get error, however I change to below
MemberList = "'007910', '006853', '007965'"
SQL = "Select MemberID, name From Member"
SQL = SQL & " Where MemberID In (" & MemberList & ")"
SQL = SQL & " ORDER BY INSTR(" & MemberList & ")"
Set rs = GetMdbStaticRecordset( "Member.mdb", SQL)
While not rs.eof
007965
007910
006853
....... Wend
The results come out, but it seems no sorting at all.
I also try all the following :
SQL = SQL & " ORDER BY INSTR('" & MemberList & "')"
SQL = SQL & " ORDER BY INSTR(" & MemberList & ",MemberID)"
SQL = SQL & " ORDER BY INSTR('" & MemberList & "',MemberID)"
SQL = SQL & " ORDER BY INSTR('" & MemberList & "','MemberID')"
I am using Access2003 to create the db, and the data of MemberID is text
format.
What wrong to me? Please help !
Alan Yuan
Thanks very much
"Chris Hohmann" <nospam@thankyou.com> 撰寫於郵件新聞:%23B1NcaV6FHA.2676@TK2MSFTNGP15.phx.gbl...
>
> "Peter" <alan@neind.net> wrote in message
> news:%230YC7SV6FHA.1188@TK2MSFTNGP12.phx.gbl...
>
> SQL = SQL & "ORDER BY INSTR('" & MemberList & "',MemberID)"
>
| |
| Mark Schupp 2005-11-15, 6:55 pm |
| What is the data type of the memberid column?
--
--Mark Schupp
"Peter" <alan@neind.net> wrote in message
news:OjiHZLi6FHA.1020@TK2MSFTNGP15.phx.gbl...
> Thanks for yr help, but when I apply yr syntax,
>
> SQL = SQL & "ORDER BY INSTR('" & MemberList & "',MemberID)"
>
> the result get error, however I change to below
>
> MemberList = "'007910', '006853', '007965'"
>
> SQL = "Select MemberID, name From Member"
> SQL = SQL & " Where MemberID In (" & MemberList & ")"
> SQL = SQL & " ORDER BY INSTR(" & MemberList & ")"
> Set rs = GetMdbStaticRecordset( "Member.mdb", SQL)
>
> While not rs.eof
> 007965
> 007910
> 006853
> ....... Wend
>
> The results come out, but it seems no sorting at all.
>
> I also try all the following :
>
> SQL = SQL & " ORDER BY INSTR('" & MemberList & "')"
> SQL = SQL & " ORDER BY INSTR(" & MemberList & ",MemberID)"
> SQL = SQL & " ORDER BY INSTR('" & MemberList & "',MemberID)"
> SQL = SQL & " ORDER BY INSTR('" & MemberList & "','MemberID')"
>
> I am using Access2003 to create the db, and the data of MemberID is text
> format.
>
> What wrong to me? Please help !
>
> Alan Yuan
> Thanks very much
>
>
>
> "Chris Hohmann" <nospam@thankyou.com>
> 撰寫於郵件新聞:%23B1NcaV6FHA.2676@TK2MSFTNGP15.phx.gbl...
>
>
| |
| Chris Hohmann 2005-11-15, 6:55 pm |
| "Peter" <alan@neind.net> wrote in message
news:OjiHZLi6FHA.1020@TK2MSFTNGP15.phx.gbl...
> Thanks for yr help, but when I apply yr syntax,
>
> SQL = SQL & "ORDER BY INSTR('" & MemberList & "',MemberID)"
Try this instead:
SQL = SQL & "ORDER BY INSTR(""" & MemberList & """,MemberID)"
| |
|
| It works, thanks for yr help very very much.
"Chris Hohmann" <nospam@thankyou.com> 撰寫於郵件新聞:eF0Egaj6FHA.1720@TK2MSFTNGP09.phx.gbl...
> "Peter" <alan@neind.net> wrote in message
> news:OjiHZLi6FHA.1020@TK2MSFTNGP15.phx.gbl...
>
> Try this instead:
>
> SQL = SQL & "ORDER BY INSTR(""" & MemberList & """,MemberID)"
>
|
|
|
|
|