Multiple select statement 1

[Richey]

Hello

I've got a search page with one text box 'txtmodel' and 2 drop-down boxes 'drpmanufacturer' and 'drpmake'
I want the user to be able to search on one, any two or all of the fields. How can I build up the multiple select statement after saying, on the response page

strmodel = request.form("model&quot
etc etc

Thanks
Richey

 

[link9]

I'll assume that the default selected values (i.e. what they'll be if user selects nothing) is an empty string.

txtModel = request.form("txtModel&quot
drpManufacturer = request.form("drpmanufacturer&quot
drpMake = request.form("drpmake&quot

dim sql, alreadyWhered
alreadyWhered = false
sql = "SELECT * FROM tableName "

if drpManufacturer <> &quot;&quot; then
if not alreadyWhered then
sql = sql & &quot;WHERE colName = '&quot; & drpManufacturer & &quot;' &quot;
alreadyWhered = true
else
sql = sql & &quot;AND colName = '&quot; & drpManufacturer & &quot;' &quot;
end if
end if

if drpMake <> &quot;&quot; then
if not alreadyWhered then
sql = sql & &quot;WHERE colName = '&quot; & drpMake & &quot;' &quot;
alreadyWhered = true
else
sql = sql & &quot;AND colName = '&quot; & drpMake & &quot;' &quot;
end if
end if

if txtModel <> &quot;&quot; then
if not alreadyWhered then
sql = sql & &quot;WHERE colName = '&quot; & txtModel & &quot;' &quot;
alreadyWhered = true
else
sql = sql & &quot;AND colName = '&quot; & txtModel & &quot;' &quot;
end if
end if

This method can (and should) be easily abstracted into a function that is called n number of times, using inputs to specify which column you will filter by. It'll run more efficiently that way.

Of course, you'll have to replace all my 'colName's with the actual names of the columns you want to filter by, and my 'tableName' with the name of the table, but that should get you started.


Paul Prewett