I am trying to search an Access database, using data entered from a form. The form offers users a choice of different text fields in which they can search the database with. It would be great if they could enter data in a number of the text fields to broaden the search, if this is possible? Could anyone please suggest how I can get the code below to do this, or if a multiple field search is not possible, then a single field search, but where the user still has a choice of which field to search by. I would be most appreciative of any help.<br><br><br><html><br><head><br><title>Brigantia Directory Search Results</title><br></head><br><body bgcolor="#FFFFFF"><br><b><i><font color="#000099" face="Verdana, Arial, Helvetica, sans-serif" size="4">Brigantia <br>Directory Search Results</font></i></b><br><br><br><hr><br><br><% <br> Business= request.form("company"
<br> Postcode= request.form("postcode"<br> Stdcode= request.form("stdcode"<br> Town= request.form("town"<br><br> set rs=server.createobject("adodb.recordset"<br><br><br> if Business = "" then<br> response.end<br> else<br> sql = "select * from tablebrig "<br> sql = sql& " where lastname='" & Business & "';"<br> end if<br> <br> <br> if postcode = "" then<br> response.end<br> else<br> sql = "select * from tablebrig "<br> sql = sql& " where postcode='" & Postcode & "';"<br> end if<br> <br><br><br> if Stdcode = "" then<br> response.end<br> else<br> sql = "select * from tablebrig "<br> sql = sql& " where stdnumber='" & Stdcode & "';"<br> end if<br> <br> <br> <br> if Town = "" then<br> response.end<br> else<br> sql = "select * from tablebrig "<br> sql = sql& " where town='" & Town & "';"<br> end if<br><br> <br> rs.open sql, "DSN=brigantia"<br> <br><br> rs.MoveFirst<br> response.write "<table border=1>"<br> Do while not rs.eof <br> response.write "<tr><td>" & rs("lastname" & "</td>"<br> response.write "<td>" & rs("postcode" & "</td>"<br> response.write "<td>" & rs("stdnumber" & "</td>"<br> response.write "<td>" & rs("city" & "</td></tr>" <br> rs.movenext<br> loop<br> response.write "</table>"<br> rs.close<br> set rs=nothing<br> <br> <br>%><br><br><hr><br><br>End of Page</p><br></body><br></html><br><br>
<br> Postcode= request.form("postcode"<br> Stdcode= request.form("stdcode"<br> Town= request.form("town"<br><br> set rs=server.createobject("adodb.recordset"<br><br><br> if Business = "" then<br> response.end<br> else<br> sql = "select * from tablebrig "<br> sql = sql& " where lastname='" & Business & "';"<br> end if<br> <br> <br> if postcode = "" then<br> response.end<br> else<br> sql = "select * from tablebrig "<br> sql = sql& " where postcode='" & Postcode & "';"<br> end if<br> <br><br><br> if Stdcode = "" then<br> response.end<br> else<br> sql = "select * from tablebrig "<br> sql = sql& " where stdnumber='" & Stdcode & "';"<br> end if<br> <br> <br> <br> if Town = "" then<br> response.end<br> else<br> sql = "select * from tablebrig "<br> sql = sql& " where town='" & Town & "';"<br> end if<br><br> <br> rs.open sql, "DSN=brigantia"<br> <br><br> rs.MoveFirst<br> response.write "<table border=1>"<br> Do while not rs.eof <br> response.write "<tr><td>" & rs("lastname" & "</td>"<br> response.write "<td>" & rs("postcode" & "</td>"<br> response.write "<td>" & rs("stdnumber" & "</td>"<br> response.write "<td>" & rs("city" & "</td></tr>" <br> rs.movenext<br> loop<br> response.write "</table>"<br> rs.close<br> set rs=nothing<br> <br> <br>%><br><br><hr><br><br>End of Page</p><br></body><br></html><br><br>