in vb6, how to query access records, then order by specific ids

Question

select * from [main] where id in (33,11,22) order by id

if i using this query, i will get the recordset order by this:

11
22
33

but i want to the return recordset by this order (same as id in (33,11,22) order):

33
11
22

by google i understand we can use this to realize:

select * from [main] where id in (33,11,22) order by charindex(ltrim(id),'33,11,22')

but the problem is vb6 does not support charindex, I got the error The expression contains undefined function call CHARINDEX when I using it.

so what can I do in vb6? thank you.

update: by @MarkL's suggest, I'm using instr to instead of CHARINDEX, the query running but not running as expectation, the return recordset with this order:

33
22
11

thanks.

Answer 1

select * from [main] where id in (33,11,22) order by InStr("33,11,22", id)

Should give you what you want.

A better version to guard against ID 11 matching "111,222" would be:

select * from [main] where id in (33,11,22) order by InStr(",33,11,22,", "," & id & ",")

A set based alternative would be to have a 2nd "matches" table with a AutoNumber RowNum column & a Value column into which you insert 33, 11, 22 (in order) allowing for:

select * from [main] inner join matches on (matches.id = [main].id) order by rownum