Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

"Between AND Operator" in query

Status
Not open for further replies.

CHTHOMAS

Programmer
Jun 16, 1999
106
AE
I have an access table with a field "period" of Text data type. I have to retrieve records from the table between 2 periods using a select query. For retrieving records between periods 1 and 3, When i use "Between AND Operator" in query, then it returns all record which have a 1 in its period field. ie 1,11,12,13 etc.. If i give say 10 for starting period and 13 for ending period then it returns the record correctly. I figured out its the text data type causing the problem. I tried coverting the text data type to numbr using the following functions. "cint", "val", "clng" etc. But still am getting the same results. Can someone tell me whats going wrong?


Regards,

Charley
 
Charley,

You can retrieve the text representation of your numbers if
you want, but the criteria should be:

Between CLng(txtNumber1) And CLng(txtNumber2)

And if you need to order the query, make a new field and
order it ascending, and make it invisible:

NewNumber: CLng(txtNumber1)

Wayne
 
WayneRyan,

Well i don't have any problem in converting text to number. When i use the Between And operator for the converted field (ie number field), am getting the same results as i get for the text field which is confusing me.

Regards,

charley
 
Create another column in your query. Set the Data Source as LongPeriod:Clng(txtPeriod).

Put the criteria on that column as say

BETWEEN 1 AND 3

Note you convert in the column definition not in the BETWEEN clause
 
Thanks. Let me have a try.

Regards,

Charley
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top