Between .. And Operator in MS Access Sql Query

[shanuragu]

HI

Please tell me - what is the error in the following query statement in ASP used to fetch records
with in Range given by the (ordno1 & ordno2 are user input values).

sqlstr = "Select o.*, d.* FROM orderdetails o,
personaldetails d WHERE o.orderreference=d.orderreference And o.orderreference BETWEEN" &ordno1& "And" &ordno2& "ORDER By o.orderreference"

I am getting syntax error Please help.

It is very urgent.

ShaRA

[shanuragu]

Hi

I am getting the following error after using the code u had given. Also ordno1 & ordno2 are text fields in the database.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Between operator without And in query expression '((o.orderreference=d.orderreference) And (o.orderreference BETWEEN(55 And 60)))'.

[shanuragu]

Thanks Dave

It is working Great!!!!
ShaRa

[shanuragu]

Hi

Fetching set of records with in the given range works fine for a set of values using Between & And operator. But if I try to fetch records b/w 1 to 100 it is not working, ie, record count will be 0 or -ve. Why? As we discussed earlier, ordno1 & ordno2 are text fields in the data base. Is that the reason for the above problem. Can I fetch set of records using Between & And operator for text fields also? or Any other reason...Solution ????
Please help.
ShaRa :confused:

[shanuragu]

How can I get through this problem??
Any other method to fetch set of records with in the given range (for values stored as text data)??

ShaRa:mad:

[Ribeyed]

Hi,
i've been following this thread from the start and this seems strange that you are returning no records. I ran the code bellow against my own SQL server and it returned records.



<% set discoveryDB = server.createobject ("adodb.connection")
discoveryDB.open "discovery", "username", "password"
firstname = "David Bell"
sql = "SELECT tblCustomer.*, tblOrders.* FROM tblCustomer, tblOrders " & _
"WHERE tblCustomer.CustomerID = tblOrders.CustomerID AND tblCustomer.FirstName = '"&FirstName&"' AND " & _
"tblCustomer.CustomerID BETWEEN 1 AND 70 ORDER BY tblCustomer.CustomerID DESC"

set RS = discoveryDB.Execute(sql)
while not RS.EOF
response.write rs(0) & "&nbsp;"
response.write rs("FirstName") & "<BR>"

rs.movenext
wend
rs.close
set rs = nothing
discoverydb.close
set discoverydb = nothing
%>


As far as i can see there is not much differences between the 2 sql statements apart for mine being simillar to your first sql statement.
The only thing wrong with your first sql statement that i can see is the missing single quote around "ordno1" and "ordno2" because you mentioned:


Also ordno1 & ordno2 are text fields in the database.


When i tried changing the querry to

sql = "SELECT tblCustomer.*, tblOrders.* FROM tblCustomer, tblOrders " & _
"WHERE tblCustomer.CustomerID = tblOrders.CustomerID AND tblCustomer.FirstName = '"&FirstName&"' AND " & _
"tblCustomer.FirstName BETWEEN 1 AND 70 ORDER BY tblCustomer.CustomerID DESC""


Knowing that tblCustomer.FirstName is field text i got this following error.

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'David Bell' to a column of data type int.
/between.asp, line 16


Now i interpt that error message as saying i can't use BETWEEN to search a field that is text even if it contains numeric values.
:rolleyes:
I ask you why can't the ordno1 and ordno2 be numeric fields?

Hope this helps

[shanuragu]

I did change the db table field to numeric in the query itself
& it is working fine!!!

Here is the code
sqlstr = "Select o.*, d.*" & _
" FROM orderdetails as o, personaldetails as d" & _
" WHERE ((o.orderreference=d.orderreference)" & _
" And (CInt(o.orderreference) BETWEEN "&ordno1&" And "&ordno2&"))" & _
" ORDER By o.orderreference;"

Thanks for all the help & suggestions.

ShaRa