got the dropdown earch working with the help from rokslide but now I have a new problem. If I search the Searchdate and then also search for any of the other fields like firstname I get 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/1970# AND #01/02/2004# WHERE Borrowers.FirstName LIKE 'pawloski''.
/database/search_results.asp, line 65
here is the code from search_results.asp:
<%@ 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
FirstName = Trim(Request("FirstName"))
MiddleInitial = Trim(Request("MiddleInitial"))
LastName = Trim(Request("LastName"))
PhoneNumber = Trim(Request("HomePhone"))
WorkPhone = Trim(Request("WorkPhone"))
Email = Trim(Request("Email"))
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>
dBforums
>
cannot search for multiple fields
10-15-04, 15:00
Linear Mode
