"Between AND Operator" in query

[CHTHOMAS]

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

 

[WayneRyan]

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

 

[CHTHOMAS]

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

 

[cheerio]

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

 

[CHTHOMAS]

Thanks. Let me have a try.

Regards,

Charley