Hello,
I am trying to select a range of values from a table called Codes. The values are passed from two input boxes on a form.
I am sooooooooooooooo close but sooooooooooooooooooooooo far away. I know the problems is in the request.form() part of my code. If I insert numbers in place of the request.form() it returns the correct data. Code is defined as a number in the database (Access 2000)

This is the formula for my logic
SELECT "column_name" FROM "table_name"WHERE "column_name" BETWEEN 'value1' AND 'value2'
************************************************** ***********

qry = "SELECT * FROM LA_Codes WHERE Code BETWEEN 10021 AND 10071 "
This works but I need the user to enter the data in a form to search for.
************************************************** ************************************************** **


line 21
"SELECT * FROM LA_Codes "WHERE Code BETWEEN request.form("searchCode2") AND request.form("searchCode3")

Generates this error:
Microsoft VBScript compilation error '800a0401'
Expected end of statement
/remmdconnect.asp, line 21
qry = "SELECT * FROM LA_Codes "WHERE Code BETWEEN request.form("searchCode2") AND request.form("searchCode3")
-------------------------------^
************************************************** ************************************************** *********************************
dim code1=request.form("searchCode2")
dim code2=request.form("searchCode3")
line 21
qry = "SELECT * FROM LA_Codes WHERE Code BETWEEN code1 AND code2 "
line 22
Set oRS = oConn.Execute(qry)

Generates this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
/remmdconnect.asp, line 22
************************************************** ************************************************** *****************
line21
qry = "SELECT * FROM LA_Codes WHERE Code BETWEEN 'code1' AND 'code2' "
line 22
Set oRS = oConn.Execute(qry)
Generates this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
/remmdconnect.asp, line 22
************************************************** ************************************************** *****************
"SELECT * FROM LA_Codes WHERE Code BETWEEN & request.form('searchCode2') AND request.form('searchCode3') "
Generates this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Code BETWEEN & request.form('searchCode2') AND request.form('searchCode3')'.
/remmdconnect.asp, line 22

Can someone point me in the right direction.
thank you
dink

Hi Dink,

Try the following:

code1=request.form("searchCode2")
code2=request.form("searchCode3")

qry = "SELECT * FROM LA_Codes WHERE Code BETWEEN '" & code1 & "' AND '" & code2 & "'"

Be sure to include the spaces after the BETWEEN and around the AND so things don't run together.

Hope this helps,
David

Hi Dave,
I tried:
qry = "SELECT * FROM LA_Codes WHERE Code BETWEEN '" & code1 & "' AND '" & code2 & "'"

It generated this error?

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/remmdconnect.asp, line 22

dink

For one thing you will want to get rid of the single quotes.
I assume the datatype of the Code column is numeric.

Woody Z
http://www.learntoprogramnow.com

Thanks woodyz,
taking out the single quotes did the trick!
Thank you!!!
dink

No prob. Be sure to thank David as well, as he did a good job in showing your basic issue - I just fine tuned it.

Woody Z
http://www.learntoprogramnow.com

I also want to thank you David!
I could not have resolved this problem without the help of you both.
thank you!
dink