Hi: I need to search records based on min and max values which are input by a user. How do I handle numbers in the SQL select statement? Do I need to define min and max as numbers specifically? When I run the following SQL statement: "select * from delamo where min >= '"&min&"'", I got an error message saying data type mismatch. Please help me! Thank you very very much in advance.

what u will need to do is decalre and set variables to the min and max values, then u should be able to use these with the SQl. On integer values you do not need any quation marks around the variable nams in the sql..

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
what u will need to do is decalre and set variables to the min and max values, then u should be able to use these with the SQl. On integer values you do not need any quation marks around the variable nams in the sql..
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Hi: Thanks. Did you mean I need to dim it as a variable? I tried but still didn't work. If I put a number in the sql select statement it worked but when I put a variable from form text box (request.form("min") it didn't work. What should I do with it? Please help!

Hi

In a SQL Stored Procedure you can use something like this:

CREATE PROCEDURE sp_My_MinMax

@Min int,
@Max int

AS

Select * from myTable where myColumn between @Min and @Max

And from a select statement in a web page it's hard to describe as you don't tell us what web server you're using. Create a form (POST) with two text boxes or drop downs and name them myMin and myMax respectively.

For ASP something like this:

Dim myMin, myMax
myMin = Request.Form("myMin"
myMax = Request.Form("myMax"

etc, etc, etc...

"SELECT * FROM myTable WHERE (myColumn BETWEEN '" & myMin & "' AND '" & myMax & "')"

Give us some more to work with and we'll help you out.

Digga

Sharing Knowledge Saves Valuable Time!!!

Edited by - Digga the Wolf on 17 Jul 2003 17:06:53

Hi: Thank you so very much to help me. I tried your code but still got the error message "Data type mismatch in criteria expression." Here is my code, do you have time to check it out for me please? Thanks much again, I really your help.
***************
dim myrs, mysql, rcount, min, max
set myrs=server.createobject("adodb.recordset"
min=int(request.form("min")
max=int(request.form("max")
if min &lt;&gt;"" and isnumeric(min) and max &lt;&gt;"" then
mysql="select Center_name, Store_name, Address, Phone, Size, Type, APN from delamo where (size between '"& min &"' and '"& max &"')"
end if
myrs.open mysql, myconn

Hi

Remove the single quotes from your Select statement that surround the variables min and max.

Digga

Sharing Knowledge Saves Valuable Time!!!

Thanks very much to the ones who replied and helped me out. Since my data is a number data type the correct SQL statement should be:
"SELECT * FROM myTable WHERE size BETWEEN " & MyMin & "and " & MyMax