Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Oct 2004
    Posts
    43

    Unanswered: dropdown search for dates

    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>
    <!--#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">&nbsp;&nbsp;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">&nbsp;</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

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    um,... no submit results page for me to see.

  3. #3
    Join Date
    Oct 2004
    Posts
    43

    here is the search results 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

    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>
    <!--#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">&nbsp;</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">&nbsp;</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">&nbsp;</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">&nbsp;</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>

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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?

  5. #5
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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

  6. #6
    Join Date
    Oct 2004
    Posts
    43
    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

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

  8. #8
    Join Date
    Oct 2004
    Posts
    43
    THANK YOU ROCKSLIDE!!!

    You are a frickin genius

    works great

  9. #9
    Join Date
    Oct 2004
    Posts
    43
    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??

  10. #10
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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

  11. #11
    Join Date
    Oct 2004
    Posts
    43
    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

  12. #12
    Join Date
    Oct 2004
    Posts
    43
    if i just search for any other field other than the date it works

  13. #13
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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

  14. #14
    Join Date
    Oct 2004
    Posts
    43
    how can I fix it so it works right? any suggestions

  15. #15
    Join Date
    Oct 2004
    Posts
    43
    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 '

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •