Placing certain records in a recordset first, then remaining records..

[Rexolio]

I want to display all records I have in a database for my users. Easy enough. However, I want them to do a search for the records they want so that the FIRST records are records that match their criteria, then the remaining records follow in a loop WITHOUT the records that matched their criteria! I know how to do a recordset and I know how to do the LIKE function, but I don't know how to do it all with in the same recordset, as I don't want to repeat the searched records.

Does this make sense?

USER SEARCHES FOR: Red, Blue

RECORDSET:
Rec4 - Red *
Rec6 - Blue *
Rec1 - Yellow
Rec2 - Green
Rec3 - Orange
Rec5 - Black
Rec7 - White

 

[jfriestman]

use a union query to create the recordset

select field1 from table1 where field2 like '%red%'
union
select field1 from table1 where field2 not like '%red%'

get it?

only catch - if you order the table you lose your ranking. there is a way around it but involves using sql to create temporary tables.

Jeff Friestman
jfriestman@hotmail.com

http://www.brainbench.com/transcript.jsp?pid=61192

Brainbench 'Most Valuable Professional' for ASP

Brainbench 'Most Valuable Professional' for JavaScript

http://www.brainbench.com