Between expression and text fields

[mscurto]

I want to create a query using the criteria that the query only
returns numbers within a specific range. The catch is that this is a
text field, not a number. What is the Syntax for the Between command
to do this?

This is what I am currently using that doesn't work. Do I need to
convert the text to a numeric data type?

Between "19316" And "19396"

[JustListenen]

On May 8, 1:36 pm, [e-mail] wrote:

I want to create a query using the criteria that the query only
returns numbers within a specific range. The catch is that this is a
text field, not a number. What is the Syntax for the Between command
to do this?

This is what I am currently using that doesn't work. Do I need to
convert the text to a numeric data type?

Between "19316" And "19396"

You have the correct syntax for a query created in the QBE grid but
not a Query created as a VBA string.

In VBA the query would look like this

Dim strSQL
strSQL = "Select YourTable.YourField from YourTable where
YourTable.YourField between '16316' and '19396'"

Note the single quotes surrounding the values of the text fields

[paii, Ron]

<ms*****@gmail.comwrote in message
news:11**********************@y5g2000hsa.googlegro ups.com...

I want to create a query using the criteria that the query only
returns numbers within a specific range. The catch is that this is a
text field, not a number. What is the Syntax for the Between command
to do this?

This is what I am currently using that doesn't work. Do I need to
convert the text to a numeric data type?

Between "19316" And "19396"

The above criteria will also return "1932". Access treats numbers stored as
text like any other text, left to right. If this is a problem convert to
numeric or format with leading zeros so all the strings have the same
length.