I have a search form that looks for a bunch of different fields. I have the first field called Searchdate and it is a dropdown and you can look for a choice of four different fields Like Stipdate and search between startdate and end date. That portion works fine if you just use it only, but if I want to search for searchdate and for lastname or any other of the other fields I keep getting the following error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'stipsdate BETWEEN #01/01/04# AND #01/02/2004# WHERE Borrowers.LastName LIKE 'pawloski''.
/database/search_results.asp, line 67
I cant figure out what I am doing wrong. Here is the code from my search form:
<%@ LANGUAGE=VBScript %>
<%
Option Explicit
Response.Buffer = True
Dim myfield
myfield = Trim(Request("myfield"))
On Error Resume Next ' go to next line if there's an error
Session("SSS_query") = ""
%>
<html>
<head>
<title>Borrowers Search</title>
<link rel="stylesheet" href="styles.css" type="text/css">
</head>
<body topmargin=0 leftmargin=0 marginheight=0 marginwidth=0>
<!--#include file="header.inc" -->
<img src="logo.gif" width="144" height="75">
<form name="search" method="POST" action="search_results.asp?pageNumber=0"><div align="center">
<table width="600" cellpadding="0" cellspacing="0" border="1" bordercolor="#000000">
<tr>
<td class="strip"> SEARCH RECORDS</td>
</tr>
<tr>
<td>
<table cellspacing="0" cellpadding="0" border="0" bgcolor="#cccccc" width="100%">
<tr>
<td height="16" colspan="3" valign="top">
<hr class="line" size="1">
</td>
</tr>
<tr>
<td width="194" valign="top">
<div align="right">
<table cellpadding="1" cellspacing="0" width="80%">
<tr>
<td class="fieldname">Submit Date</td>
</tr>
</table>
</div>
</td>
<td width="6" valign="top"></td>
<td width="410" valign="top" class="data1">
<select name="searchDate" id="searchDate">
<option value="stipsdate" selected>Stips Date</option>
<option value="signdate">Sign Date</option>
<option value="apporvaldate">Approval Date</option>
<option value="funddate">Fund Date</option>
</select>
From
<input name="Startdate" type="text" value="" size="8">
To
<input name="enddate" type="text" value="" size="8"></td>
</tr>
<tr>
<td width="194" height="1" valign="top"><img width="194" height="1" src="transparent.gif"></td>
<td width="6" height="1" valign="top"><img width="6" height="1" src="transparent.gif"></td>
<td width="410" height="1" valign="top"><img width="410" height="1" src="transparent.gif"></td>
</tr>
<tr>
<td height="16" colspan="3" valign="top">
<hr class="line" size="1">
</td>
</tr>
<tr>
<td width="194" valign="top">
<div align="right">
<table cellpadding="1" cellspacing="0" width="80%">
<tr>
<td class="fieldname">FirstName</td>
</tr>
</table>
</div>
</td>
<td width="6" valign="top"></td>
<td width="410" valign="top" class="data1">
<input type="text" name="FirstName" value="">
</td>
</tr>
<tr>
<td width="194" height="1" valign="top"><img width="194" height="1" src="transparent.gif"></td>
<td width="6" height="1" valign="top"><img width="6" height="1" src="transparent.gif"></td>
<td width="410" height="1" valign="top"><img width="410" height="1" src="transparent.gif"></td>
</tr>
<tr>
<td height="16" colspan="3" valign="top">
<hr class="line" size="1">
</td>
</tr>
<tr>
<td width="194" valign="top">
<div align="right">
<table cellpadding="1" cellspacing="0" width="80%">
<tr>
<td class="fieldname">MiddleInitial</td>
</tr>
</table>
</div>
</td>
<td width="6" valign="top"></td>
<td width="410" valign="top" class="data1">
<input type="text" name="MiddleInitial" value="">
</td>
</tr>
<tr>
<td width="194" height="1" valign="top"><img width="194" height="1" src="transparent.gif"></td>
<td width="6" height="1" valign="top"><img width="6" height="1" src="transparent.gif"></td>
<td width="410" height="1" valign="top"><img width="410" height="1" src="transparent.gif"></td>
</tr>
<tr>
<td height="16" colspan="3" valign="top">
<hr class="line" size="1">
</td>
</tr>
<tr>
<td width="194" valign="top">
<div align="right">
<table cellpadding="1" cellspacing="0" width="80%">
<tr>
<td class="fieldname">LastName</td>
</tr>
</table>
</div>
</td>
<td width="6" valign="top"></td>
<td width="410" valign="top" class="data1">
<input type="text" name="LastName" value="">
</td>
</tr>
<tr>
<td width="194" height="1" valign="top"><img width="194" height="1" src="transparent.gif"></td>
<td width="6" height="1" valign="top"><img width="6" height="1" src="transparent.gif"></td>
<td width="410" height="1" valign="top"><img width="410" height="1" src="transparent.gif"></td>
</tr>
<tr>
<td height="16" colspan="3" valign="top">
<hr class="line" size="1">
</td>
</tr>
<tr>
<td width="194" valign="top">
<div align="right">
<table cellpadding="1" cellspacing="0" width="80%">
<tr>
<td class="fieldname">HomePhone</td>
</tr>
</table>
</div>
</td>
<td width="6" valign="top"></td>
<td width="410" valign="top" class="data1">
<input type="text" name="HomePhone" value="">
</td>
</tr>
<tr>
<td width="194" height="1" valign="top"><img width="194" height="1" src="transparent.gif"></td>
<td width="6" height="1" valign="top"><img width="6" height="1" src="transparent.gif"></td>
<td width="410" height="1" valign="top"><img width="410" height="1" src="transparent.gif"></td>
</tr>
<tr>
<td height="16" colspan="3" valign="top">
<hr class="line" size="1">
</td>
</tr>
<tr>
<td width="194" valign="top">
<div align="right">
<table cellpadding="1" cellspacing="0" width="80%">
<tr>
<td class="fieldname">WorkPhone</td>
</tr>
</table>
</div>
</td>
<td width="6" valign="top"></td>
<td width="410" valign="top" class="data1">
<input type="text" name="WorkPhone" value="">
</td>
</tr>
<tr>
<td width="194" height="1" valign="top"><img width="194" height="1" src="transparent.gif"></td>
<td width="6" height="1" valign="top"><img width="6" height="1" src="transparent.gif"></td>
<td width="410" height="1" valign="top"><img width="410" height="1" src="transparent.gif"></td>
</tr>
<tr>
<td height="16" colspan="3" valign="top">
<hr class="line" size="1">
</td>
</tr>
<tr>
<td width="194" valign="top">
<div align="right">
<table cellpadding="1" cellspacing="0" width="80%">
<tr>
<td class="fieldname">Email</td>
</tr>
</table>
</div>
</td>
<td width="6" valign="top"></td>
<td width="410" valign="top" class="data1">
<input type="text" name="Email" value="">
</td>
</tr>
<tr>
<td width="194" height="1" valign="top"><img width="194" height="1" src="transparent.gif"></td>
<td width="6" height="1" valign="top"><img width="6" height="1" src="transparent.gif"></td>
<td width="410" height="1" valign="top"><img width="410" height="1" src="transparent.gif"></td>
</tr>
<tr>
<td width="194" height="1" valign="top"><img width="194" height="1" src="transparent.gif"></td>
<td width="6" height="1" valign="top"><img width="6" height="1" src="transparent.gif"></td>
<td width="410" height="1" valign="top"><img width="410" height="1" src="transparent.gif"></td>
</tr>
</table>
</td>
</tr>
<tr>
<td class="strip"> </td>
</tr>
<tr>
<td width="600" colspan="2">
<div align="center">
<input type="submit" name="submit" value="SEARCH">
</div>
</td>
</tr>
</table>
</div>
</form>
<br>
<div align="center"><br>
<table width="540" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
<div align="left"><font color="#000000">
Leave all fields blank to see all records. Enter information you may
know about the record you are searching for into any of the fields and
then click <b>Search</b>.<br>
<br>
Follow partial entries with the % symbol. For example, entering <b>A%</b>
into a field would narrow your search to records in which that field
contains text beginning with the letter <b>A</b>.</font></div>
</td>
</tr>
</table>
</div>
</body>
</html>
************************************************** *******
and here is the search_results.asp page:
<%@ LANGUAGE=VBScript %>
<%
Response.Buffer = True
Dim connect, recordset, whereclause, query, pageNumber, itemNumber, lineIndex
lineIndex = 0
Dim CustomerID
Dim FirstName
Dim MiddleInitial
Dim LastName
Dim HomePhone
Dim WorkPhone
Dim Email
Dim NoResults
Set connect = Server.CreateObject("ADODB.Connection") ' Prepare to connect to database
connect.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath ("db1.mdb")
pageNumber = CInt(Request("pageNumber"))
if PageNumber = 0 then
pageNumber = 1
' Get the form information in local variables
CustomerID = Trim(Request("CustomerID"))
FirstName = Trim(Request("FirstName"))
MiddleInitial = Trim(Request("MiddleInitial"))
LastName = Trim(Request("LastName"))
PhoneNumber = Trim(Request("HomePhone"))
WorkPhone = Trim(Request("WorkPhone"))
Email = Trim(Request("Email"))
Searchdate = Trim(Request("Searchdate"))
StartDate = Trim(Request("startdate"))
EndDate = Trim(Request("enddate"))
whereclause = "WHERE "
If CustomerID <> "" Then
whereclause = whereclause & "Borrowers.CustomerID LIKE '" & CustomerID & "' AND "
End If
If FirstName <> "" Then
whereclause = whereclause & "Borrowers.FirstName LIKE '" & FirstName & "' AND "
End If
If MiddleInitial <> "" Then
whereclause = whereclause & "Borrowers.MiddleInitial LIKE '" & MiddleInitial & "' AND "
End If
If LastName <> "" Then
whereclause = whereclause & "Borrowers.LastName LIKE '" & LastName & "' AND "
End If
If PhoneNumber <> "" Then
whereclause = whereclause & "Borrowers.HomePhone = " & HomePhone & " AND "
End If
If WorkPhone <> "" Then
whereclause = whereclause & "Borrowers.WorkPhone = " & WorkPhone & " AND "
End If
If Email <> "" Then
whereclause = whereclause & "Borrowers.Email LIKE '" & Email & "' AND "
End If
If Right(whereclause, 4) = "AND " Then
whereclause = Left(whereclause, Len(whereclause) - 4) ' strip off 'AND '
Else
whereclause = Left(whereclause, Len(whereclause) - 6) ' strip off 'WHERE '
End If
query = "SELECT * FROM Borrowers WHERE " & Request.Form("searchDate") & " BETWEEN #" & StartDate & "# AND #" & EndDate & "# " & whereclause
Session("SSS_query") = query
else
query = Session("SSS_query")
end if
set recordset = Server.CreateObject("ADODB.Recordset")
recordset.CursorType = 3 ' adOpenStatic
recordset.PageSize = 20
recordset.Open query, connect
if not recordset.eof then
recordset.AbsolutePage = CInt(pageNumber)
end if
%>
<HTML>
<title>Borrowers Search Results</title>
<link rel="stylesheet" href="styles.css" type="text/css">
<body topmargin=0 leftmargin=0 marginheight=0 marginwidth=0>
<!--#include file="header.inc" -->
<img src="logo.gif" width="144" height="75">
<table width="100%" cellpadding="0" cellspacing="0" border="1" bordercolor="#000000">
<tr>
<td>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="10" class="strip"> </td>
<td class="strip">CustomerID</td>
<td class="strip">FirstName</td>
<td class="strip">MiddleInitial</td>
<td class="strip">LastName</td>
<td class="strip">HomePhone</td>
<td class="strip">WorkPhone</td>
<td class="strip">Email</td>
</tr>
<%
NoResults = True
itemNumber = 0
Do While Not recordset.EOF and itemNumber < recordset.PageSize
NoResults = False
%>
<% If lineIndex MOD 2 = 0 Then %>
<tr>
<td width="10" class="data1"> </td>
<td class="data1"><a class="datalink" href="detailnew.asp?itemNumber=<%=(CInt(pageNumber ) - 1) * recordset.PageSize + itemNumber%>"><%=recordset("CustomerID")%></a></td>
<td class="data1"><%=recordset("FirstName")%></td>
<td class="data1"><%=recordset("MiddleInitial")%></td>
<td class="data1"><%=recordset("LastName")%></td>
<td class="data1"><%=recordset("HomePhone")%></td>
<td class="data1"><%=recordset("WorkPhone")%></td>
<td class="data1"><%=recordset("Email")%></td>
</tr>
<% Else %>
<tr>
<td width="10" class="data2"> </td>
<td class="data2"><a class="datalink" href="detailnew.asp?itemNumber=<%=(CInt(pageNumber ) - 1) * recordset.PageSize + itemNumber%>"><%=recordset("CustomerID")%></a></td>
<td class="data2"><%=recordset("FirstName")%></td>
<td class="data2"><%=recordset("MiddleInitial")%></td>
<td class="data2"><%=recordset("LastName")%></td>
<td class="data2"><%=recordset("HomePhone")%></td>
<td class="data2"><%=recordset("WorkPhone")%></td>
<td class="data2"><%=recordset("Email")%></td>
</tr>
<% End If %>
<%
itemNumber = itemNumber + 1
lineIndex = lineIndex + 1
recordset.MoveNext
Loop
%>
</table>
</td>
</tr>
<tr>
<td class="strip"> </td>
</tr>
</table>
<br><br>
<div align="center">
<table border="0" cellspacing="0" cellpadding="0" class="detail">
<tr>
<td>
<div align="center">
<%
If pageNumber > 1 Then
%>
<table width="150" border="1" bordercolor="#000000" bgcolor="#cccccc" cellpadding="0" cellspacing="0">
<tr>
<td class="button">
<div align="center">
<p><a class="button" href="search_results.asp?pageNumber=<%=pageNumber - 1%>">PREVIOUS</a></p>
</div>
</td>
</tr>
</table>
</div>
</td>
<td>
<div align="center"> </div>
</td>
<td>
<div align="center">
<%
End If
if not recordset.EOF then
%>
<table width="150" border="1" bordercolor="#000000" bgcolor="#cccccc" cellpadding="0" cellspacing="0">
<tr>
<td class="button">
<div align="center">
<p><a class="button" href="search_results.asp?pageNumber=<%=pageNumber + 1%>">
NEXT</a></p>
</div>
</td>
</tr>
</table>
<%
end if
%>
</div>
</td>
</tr>
</table>
<br>
<%
if NoResults = True Then
%>
Sorry, no records in the database matched your search parameters. Click Back
and try again.
<%
End If ' No match
%>
</div>
<p align="center"><br>
<div align="center">
<table width="540" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
<div align="center">Click on the hyperlinks in the leftmost column for
more information. </div>
</td>
</tr>
</table>
</div>
</BODY>
</HTML>
Linear Mode
