Page 1 of 3 123 LastLast
Results 1 to 15 of 32

Thread: less than SQL

  1. #1
    Join Date
    Oct 2004
    Posts
    43

    Unanswered: less than SQL

    how to do a less than in sql for instance I have this where clause I want to change from like to less than

    If LoanAmount <> "" Then
    whereclause = whereclause & "Borrowers.loanamount LIKE '" & loanamount & "' AND "
    End If

    find loanamount in database less than field from form

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    errr...
    Code:
    If LoanAmount <> "" Then
    whereclause = whereclause & "Borrowers.loanamount < " & loanamount & " AND "
    End If
    or am I missing something....

  3. #3
    Join Date
    Oct 2004
    Posts
    43
    well the funny thing is it did not return any errors, but it did not return the results I was looking for. I did a seach for less than 50000 and it returned everything in the database

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    you orginal one would have returned an error because you wrapped your numeric value in quote marks ( ' ) and that forced it to be recognised as a string, not a numeric....

    why you are getting everything back instead of just the bits you want I am not sure. It would depend a lot on what the rest of your sql statement said. Perhaps you could use response.write to print it out on the screen and paste it here so I can have a look.

  5. #5
    Join Date
    Oct 2004
    Posts
    43
    did a response.write and all it wrote was: SELECT * FROM Borrowers

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    okie, so the sql that is being executed is select * from [tablename] which is causing your heartache.

    the where clause you are building is not being added the the sql string....

  7. #7
    Join Date
    Oct 2004
    Posts
    43
    why would it not be added?

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    well that would really depend on how you decide it should be added. if you post the rest of the code I will have a look over it for you.

  9. #9
    Join Date
    Oct 2004
    Posts
    43
    <%@ LANGUAGE=VBScript %>
    <%
    Response.Buffer = True
    If Session("Login") = "" Then Response.redirect "index.asp"
    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
    Dim LoanOfficer
    Dim State
    Dim LoanStatus
    Dim LoanType
    Dim Company
    Dim Income
    Dim LoanAmount
    Dim Origination
    Dim Rebate
    Dim TDbalance
    Dim Creditscore
    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"))
    HomePhone = Trim(Request("HomePhone"))
    WorkPhone = Trim(Request("WorkPhone"))
    Email = Trim(Request("Email"))
    StartDate = Trim(Request("startdate"))
    EndDate= Trim(Request("enddate"))
    LoanOfficer= Trim(Request("loanofficer"))
    State =Trim(Request("state"))
    LoanStatus =Trim(Request("loanstatus"))
    LoanType =Trim(Request("loantype"))
    Company =Trim(Request("company"))
    Income = Trim(Request("income"))
    LoanAmount = Trim(Request("loanamount"))
    Origination = Trim(Request("origination"))
    Rebate = Trim(Request("rebate"))
    TdRate = Trim(Request("tdrate"))
    Tdbalance = Trim(Request("tdbalance"))
    PropertyValue = Trim(Request("propertyvalue"))
    Creditscore = Trim(Request("creditscore"))


    whereclause = "WHERE "
    If CustomerID <> "" Then
    whereclause = whereclause & "Borrowers.CustomerID LIKE '" & CustomerID & "' AND "
    End If
    If LoanOfficer <> "" Then
    whereclause = whereclause & "Borrowers.LoanOfficer LIKE '" & LoanOfficer & "' 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 HomePhone <> "" 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 State <> "" Then
    whereclause = whereclause & "Borrowers.state LIKE '" & state & "' AND "
    End If
    If LoanStatus <> "" Then
    whereclause = whereclause & "Borrowers.loanstatus LIKE '" & loanstatus & "' AND "
    End If
    If LoanType <> "" Then
    whereclause = whereclause & "Borrowers.loantype LIKE '" & loantype & "' AND "
    End If
    If Company <> "" Then
    whereclause = whereclause & "Borrowers.company LIKE '" & company & "' AND "
    End If
    If Income <> "" Then
    whereclause = whereclause & "Borrowers.income LIKE '" & income & "' AND "
    End If
    If LoanAmount3 <> "" Then
    whereclause = whereclause & "Borrowers.loanamount < " & loanamount3 & " AND "
    End If
    If Origination <> "" Then
    whereclause = whereclause & "Borrowers.origination LIKE '" & origination & "' AND "
    End If
    If Rebate <> "" Then
    whereclause = whereclause & "Borrowers.rebate LIKE '" & rebate & "' AND "
    End If
    If TdRate <> "" Then
    whereclause = whereclause & "Borrowers.tdrate LIKE '" & tdrate & "' AND "
    End If
    If TDbalance <> "" Then
    whereclause = whereclause & "Borrowers.TDbalance LIKE '" & TDbalance & "' AND "
    End If
    If PropertyValue <> "" Then
    whereclause = whereclause & "Borrowers.propertyvalue LIKE '" & propertyvalue & "' AND "
    End If
    If creditscore <> "" Then
    whereclause = whereclause & "Borrowers.creditscore LIKE '" & creditscore & "' 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 '
    Elseif Right(whereclause, 6) = "WHERE " Then
    whereclause = Left(whereclause, Len(whereclause) - 6) ' strip off 'WHERE '
    End If
    query = "SELECT * FROM Borrowers " & 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="detailnewl.asp?itemNumber=<%=(CInt(pageNumbe r) - 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="detailnewl.asp?itemNumber=<%=(CInt(pageNumbe r) - 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_resultsl.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_resultsl.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>

  10. #10
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    you'll love this... I know I do, it's my 1000th post.

    you are setting a variable called LoanAmount to be your request value... but then you are trying to use a variable called LoanAmount3,... that isn't going to work.... change this
    Code:
    If LoanAmount3 <> "" Then
     whereclause = whereclause & "Borrowers.loanamount < " & loanamount3 & " AND "
    End If
    to this.....
    Code:
    If LoanAmount <> "" Then
     whereclause = whereclause & "Borrowers.loanamount < " & LoanAmount & " AND "
    End If

  11. #11
    Join Date
    Oct 2004
    Posts
    43
    ok just so i get this right

    If LoanAmount <> "" Then
    whereclause = whereclause & "Borrowers.loanamount < " & LoanAmount & " AND "
    End If

    the first part of this LoanAmount is the field requested from the form page
    and the second Borrowers.loanamount is the field from database
    and the last LoanAmount is what?

  12. #12
    Join Date
    Oct 2004
    Posts
    43
    Congrats on 1000 post

    did not work got this error:

    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
    [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
    /database/search_resultsl.asp, line 134

  13. #13
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    okie, that will have something to do with the sql string again. response.write it out and post it... try and have a guess what might be wrong as well.... I suspect I know what it is but I wanna be sure.... and thanks.

  14. #14
    Join Date
    Oct 2004
    Posts
    43
    got same as before not adding whereclause:

    SELECT * FROM Borrowers

    I hope I am doing it right
    I add response.write above the line recordset.Open query, connect
    then open that page to see the results

  15. #15
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    did the error up again as well??? this is a bit strange, the place you are doing it sounds right. what value are you putting into the LoanAmount field??

Posting Permissions

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