dropdown search for dates

newbieasp · #1 (**permalink**) 10-14-04, 23:35

I have the following code for my search database page that i want to change. I want to incorporate a drop down box that has Stips date,Fund date, sign date, and approval date and to search between dates depending on which one you chose. I got it to work for just searching between one (Stipdate) but I dont know how to do it for a drop down field.

here is 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>

<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">
<input type="text" name="startdate" value="">
to
<input type="text" name="enddate" 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">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 submit_ressults.asp page

rokslide · #2 (**permalink**) 10-15-04, 00:17

um,... no submit results page for me to see.

newbieasp · #3 (**permalink**) 10-15-04, 00:30

<%@ 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 stipsdate 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>

<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>

rokslide · #4 (**permalink**) 10-15-04, 00:35

so effectively you want to make this bit

Code:

query = "SELECT * FROM Borrowers WHERE stipsdate BETWEEN #" & StartDate & "# AND #" & EndDate & "# " & whereclause

dynamic to that it uses a drop down box and replaces stipsdate with whatever value was selected in the serarch page yes?

rokslide · #5 (**permalink**) 10-15-04, 00:40

assuming that is what you are after add this select box to your first page.

Code:

<select name="searchDate" id="searchDate">
  <option value="stipdate" selected>Stips Date</option>
  <option value="funddate">Fund Date</option>
  <option value="signdate">Sign Date</option>
  <option value="approvaldate">Approval Date</option>
</select>

and change that sql line in your second page to this....

Code:

query = "SELECT * FROM Borrowers WHERE " & Request.Form("searchDate") & " BETWEEN #" & StartDate & "# AND #" & EndDate & "# " & whereclause

and I think you should be right

newbieasp · #6 (**permalink**) 10-15-04, 13:01

Thanks rokslide that worked for searching the drop down, but now I have a new problem. If I search the Searchdate and then also search for any of the other fields 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

rokslide · #7 (**permalink**) 10-16-04, 19:54

Yeah, your sql syntax is wrong... instead of writing this...

Code:

'stipsdate BETWEEN #01/01/1970# AND #01/02/2004# WHERE Borrowers.FirstName LIKE 'pawloski''.

you need to write this...

Code:

'stipsdate BETWEEN #01/01/1970# AND #01/02/2004# and Borrowers.FirstName LIKE 'pawloski''.

so stop the default value for your whereclause variable from being "where" and always add the "and" weather whereclause="" or not.

newbieasp · #8 (**permalink**) 10-16-04, 20:16

THANK YOU ROCKSLIDE!!!

You are a frickin genius

works great

newbieasp · #9 (**permalink**) 10-16-04, 20:21

Hey Rockslide one other quick question. If I dont want to search for the date and just leave it blank on the form and look for other fields I get this error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'BETWEEN ## AND ##'.
/database/search_results.asp, line 65

how do I make it not search for the date if there is no info filled in on the form for date??

rokslide · #10 (**permalink**) 10-16-04, 20:27

okie, in that same way you have done it previously... see the bold bit...

Code:

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 StartDate <> "" and EndDate<>"" Then
  whereclause = whereclause & Request.Form("searchDate") & " BETWEEN #" & StartDate & "# AND #" & EndDate & "# "
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 " & whereclause

newbieasp · #11 (**permalink**) 10-16-04, 20:41

tried that but got error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'Borrowers.LastName LIKE 'pawloski' AND stipsdate BETWEEN #01/01/04# AND #01/0'.
/database/search_results.asp, line 69

newbieasp · #12 (**permalink**) 10-16-04, 20:43

if i just search for any other field other than the date it works

rokslide · #13 (**permalink**) 10-16-04, 20:48

it's because of the stripping you are doing at the end.... that bolded bit to this...

Code:

If StartDate <> "" and EndDate<>"" Then
  whereclause = whereclause & Request.Form("searchDate") & " BETWEEN #" & StartDate & "# AND #" & EndDate & "# AND "
End If

newbieasp · #14 (**permalink**) 10-16-04, 21:23

how can I fix it so it works right? any suggestions

newbieasp · #15 (**permalink**) 10-16-04, 21:38

I think I got it !

Got rid of:

Else
whereclause = Left(whereclause, Len(whereclause) - 6) ' strip off 'WHERE '

and left:

If Right(whereclause, 4) = "AND " Then
whereclause = Left(whereclause, Len(whereclause) - 4) ' strip off 'AND '

used to be:

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 '

Internet Services

Web Development

Digital Marketing

Consumer Tech