Advanced search form

Hi there,

I have an advanced search form below which works fine except for the last sqlstring which needs to be linked to the other criteria.

<%
dim objconn
dim strconnect
dim objrs
dim sqlstring

set objconn = Server.CreateObject("ADODB.Connection")
strconnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " & Server.MapPath("whos_who.mdb")

objconn.open strconnect
Set objrs = Server.CreateObject("ADODB.RecordSet")

sqlstring = " SELECT Personnel.*, specialists.spu_id " & _
" FROM Personnel, specialists "

If Request.form("name") <> "" Then
sqlstring = sqlstring & " AND first_name LIKE '%" & Request.form("name") & "%'"
End If
If Request.form("surname") <> "" Then
sqlstring = sqlstring & " AND surname LIKE '" & Request.form("surname") & "'"
End If
If Request.form("ext") <> "" Then
sqlstring = sqlstring & " AND extension_id LIKE '" & Request.form("ext") & "'"
End If
If Request.form("initials") <> "" Then
sqlstring = sqlstring & " AND initials LIKE '%" & Request.form("initials") & "%'"
End If
If Request.form("mob") <> "" Then
sqlstring = sqlstring & " AND mobile_no LIKE '%" & Request.form("mob") & "%'"
End If
If Request.form("tel") <> "" Then
sqlstring = sqlstring & " AND ddl_no LIKE '%" & Request.form("tel") & "%'"
End If
If Request.form("fax") <> "" Then
sqlstring = sqlstring & " AND fax_ddl LIKE '%" & Request.form("fax") & "%'"
End If
If Request.form("email") <> "" Then
sqlstring = sqlstring & " AND email LIKE '%" & Request.form("email") & "%'"
End If
If Request.form("tk") <> "" Then
sqlstring = sqlstring & " AND timekeep_id LIKE '%" & Request.form("tk") & "%'"
End If
If Request.form("office") <> "" Then
sqlstring = sqlstring & " AND office = " & Request.form("office")
End If
If Request.form("division") <> "" Then
sqlstring = sqlstring & " AND division = " & Request.form("division")
End If
If Request.form("department") <> "" Then
sqlstring = sqlstring & " AND dept_id = " & Request.form("department")
End If
If Request.form("team") <> "" Then
sqlstring = sqlstring & " AND team = " & Request.form("team")
End if
If Request.form("spec") <> "" Then
sqlstring = " SELECT Personnel.*, specialists.spu_id " & _
" FROM Personnel, specialists " & _
" WHERE Personnel.p_id = specialists.p_id " & _
" AND specialists.spu_id = " & CInt(request.form("spec"))
End If
response.write request.form("spec")
objrs.open sqlstring, objconn, 3, 3

%>

Could anyone give me some advice on how to do this?

thanx in advance

First the "SELECT __ FROM table, table" format isn't a clear or reliable way of getting information from 2 tables together. You're better of doing a LEFT JOIN, INNER JOIN, or RIGHT JOIN as you're explicitly telling the db what to do if there isn't matching information in either table.

That mostly applies to the initial part

Code: Select all

sqlstring = " SELECT Personnel.*, specialists.spu_id " & _ 
" FROM Personnel, specialists "

not knowing enough about how your db is setup I could only guess that a LEFT JOIN would be the best one, so

Code: Select all

sqlstring = " SELECT Personnel.*, specialists.spu_id " & _ 
" FROM Personnel LEFT JOIN specialists ON Personnel.p_id=specialists.spu_id WHERE p_id<>'' "

This will return a recordset with everything in Personnel along with the Specialists.spu_id when there's one that matches (otherwise a NULL).

Then you can filter that with your WHERE statements; notice I added the WHERE to the end of the initial string.

Also, because each of your if...then statements adds on an "AND ____" you need something to go before the first AND, so you don't end up with "WHERE AND blah=blah" in the final string. So after WHERE i put on something that should always be true, and now you'll get "WHERE p_id<>'' AND ____" (those are 2 single quotes after<>)