Order by array

P

Peter

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 !
 
C

Chris Hohmann

Peter said:
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)"
 
P

Peter

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
 
C

Chris Hohmann

Peter said:
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)"
 

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

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top