I've seen a few time ago somone asking help for an complex search form. I cant remember what post but the search page was to complex to at least be shure that you wont miss something from the search.
I've been using an simple idee for some time and it seems that it works for any amount of search fields.
I'll make this example with 4 parameters.
database - clients id - numeric name - varchar(text) ocupation - varchar(text) age - numeric
i'll assume that the search will be after name,ocupation and age with and display order
I've received a good sugestion to add some warnings about how vulnerable is this code to "SQL injection attacks". This is true and the code presented here should be used as guideline and should be propper sanitized against SQL injection attacks.
'setting up the query parts 'if name it's empty it search for every name if name<>"" then sql_name=" AND name like '%"&name&"%'" end if
'if ocupation it's empty it search for every ocupation if ocupation<>"" then sql_ocupation=" AND ocupation like '%"&ocupation&"%'" end if
'if age it's empty it search for every age if age<>"" then sql_age=" AND age="&age end if
if order<>"" then sql_order=order else 'default order sql_order="id" end if
'building up the main query simple and easy sql="select * from clients where 1=1 " & sql_name & " " & sql_ocupation & " " & sql_age & " order by " & sql_order
This should solve all the problems and complications in your search criteria. The mai trick is using "where 1=1" thing which gives you the liberty to add to each condition easy in your main sql statement.