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

errr...
or am I missing something....

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

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.

did a response.write and all it wrote was: SELECT * FROM Borrowers

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

why would it not be added?

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.

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

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
to this.....

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?

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

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.

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

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