multiple variable db search - loop?

[thatch]

i have a form (22 different criteria although I'm only showing 3) and want to be able to enter criteria into and have it search the database.

<tr>
<td width="125"><strong>Name</strong></td>
<td width="150"><input name="cnamet" type="text" id="cnamet"></td>
</tr>
<tr>
<td width="125"><strong>WebID</strong></td>
<td width="150"><input name="cwebidt" type="text" id="cwebidt"></td>
</tr>
<tr>
<td width="125"><strong>City</strong></td>
<td width="150"><input name="ccityt" type="text" id="ccityt"></td>
</tr>

the problem is that some fields may be empty which causes me to have elaborate IF statements in the results page. is there a simpler way of being able to do this? as you can see even with 3 criteria the IF's are getting out of hand...

sql = "Select * from Vendor "
 
If Request.Form("cwebidt") = "" AND Request.Form("cnamet") = "" AND Request.Form("ccityt") = "" Then
Response.Redirect("/searchadv.asp")
End If
If Request.Form("cwebidt") = "" AND Request.Form("cnamet") = "" AND Request.Form("ccityt") <> "" Then
sql = sql & "WHERE VendorAddressCity Like '%" & Request.Form("ccityt") & "%'"
End If
If Request.Form("cwebidt") = "" AND Request.Form("cnamet") <> "" AND Request.Form("ccityt") = "" Then
sql = sql & "WHERE Name Like '%" & Request.Form("cnamet") & "%'"
End If
If Request.Form("cwebidt") <> "" AND Request.Form("cnamet") = "" AND Request.Form("ccityt") = "" Then
sql = sql & "WHERE CustomFieldWebID Like '" & Request.Form("cwebidt") & "%'"
End If
If Request.Form("cwebidt") <> "" AND Request.Form("cnamet") <> "" AND Request.Form("ccityt") = "" Then
sql = sql & "WHERE CustomFieldWebID Like '" & Request.Form("cwebidt") & "%' AND Name Like '%" & Request.Form("cnamet") & "%'"
End If
If Request.Form("cwebidt") <> "" AND Request.Form("cnamet") = "" AND Request.Form("ccityt") <> "" Then
sql = sql & "WHERE CustomFieldWebID Like '" & Request.Form("cwebidt") & "%' AND VendorAddressCity Like '%" & Request.Form("ccityt") & "%'"
End If
If Request.Form("cwebidt") = "" AND Request.Form("cnamet") <> "" AND Request.Form("ccityt") <> "" Then
sql = sql & "WHERE Name Like '%" & Request.Form("cnamet") & "%' AND VendorAddressCity Like '%" & Request.Form("ccityt") & "%'"
End If

[pete_m]

Try something like this:

----

sql = "Select * from Vendor where 1=1"

If Request.Form("ccityt") <> "" Then
sql &= " AND VendorAddressCity Like '%" & Request.Form("ccityt") & "%'"
End If
If Request.Form("cnamet") <> "" Then
sql &= " AND Name Like '%" & Request.Form("cnamet") & "%'"
End If
If Request.Form("cwebidt") <> "" Then
sql &= " AND CustomFieldWebID Like '%" & Request.Form("cwebidt") & "%'"
End If

[thatch]

THANKS!

I had to change the sql statements a bit

If Request.Form("ccityt") <> "" Then 
sql = sql & " AND VendorAddressCity Like '%" & Request.Form("ccityt") & "%'" 
End If 
If Request.Form("cnamet") <> "" Then 
sql = sql & " AND Name Like '%" & Request.Form("cnamet") & "%'" 
End If 
If Request.Form("cwebidt") <> "" Then 
sql = sql & " AND CustomFieldWebID Like '%" & Request.Form("cwebidt") & "%'" 
End If

but it works great! i would have never thought of using the 1=1 logic...

[mattur]

Or you could build the where condition(s) in a separate variable, and then just crop off the first AND:

[build strSqlWhere with IF statements]

If strSqlWhere <> "" Then
strSqlWhere = Right(strSqlWhere, Len(strSqlWhere) - 4)
End If
sql = sql & strSqlWhere