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