Searching a recordset with a dropdown list and a text field 1

[mlawson]

Another ASP into UD code snippet I've used. Sometimes it's useful to offer a way to filter the search for users, especially if you're dealing with a large and complex recordset, eg this was used to search for members of a chamber of commerce that were listed within the database by all the usual fields (company name, town etc) and were also classified by the type of business.
I came up with a way to allow both a category (type) dropdown list to be used with or without a text field. For example you may want to search for all computer service companies but only deal with those offering network support. The text field was matched against the company activity (a 100 word description in the database). So you could search for 'computer services' with 'network' in the textfield or search all 'computer services' by leaving the text field ("keyword&quotempty.

ENough of this, where's the code?

Dim SqlVar

SqlVar = "SELECT * FROM qryBusCategory"

If Request.QueryString("keyword&quot = "CompanyDescription" Then
SqlVar = SqlVar & " WHERE BusCategory LIKE '%" & _
Request.QueryString("Type&quot & "%'" & " WHERE CompanyDescription LIKE '%" & _
Request.QueryString("keyword&quot & "%'"
Else
If Request.QueryString("keyword&quot = "" Then
SqlVar = SqlVar & " WHERE BusCategory LIKE '%" & _
Request.QueryString("Type&quot & "%'"
Else
If Request.QueryString(&quot;Type&quot <> &quot;BusCategory&quot; Then
SqlVar = SqlVar & &quot; WHERE CompanyDescription LIKE '%&quot; & _
Request.QueryString(&quot;keyword&quot & &quot;%'&quot;
End If
End If
End If
Again, this is basic ASP but with some tinkering it can work well within the framework of a UD project.

Any probs, post away.

Have fun.

M