Between ....And

[Guest]

I am creating a query that is looking for a all numbers in my table that are
betwween 11100 and 69979. my result is bringing me numbers that are less
than 11100 (like 12) and numbers that are beyond 69979 (like 299825) . what
am i doing wrong in my criteria?

 

[Guest]

Suppose you show us your criteria. Maybe then we can tell.

While you're at it, right click on the Query Builder and select SQL View,
then copy and paste the SQL displayed into your email. Get back to the QEB by
right clicking and selecting Query Design.

Good Luck!

 

[Van T. Dinh]

It sounds to me that the data type of your Field values are Text and not
Numeric. Text values will be sorted exactly as you described.

 

[Guest]

SELECT [Procedures].[Physician], [Procedures].[CPTCode],
[Procedures].[Description], [Procedures].[Charges]
FROM Procedures
WHERE ((([Procedures].[CPTCode]) Between "11100" And "69979"));

 

[John Vinson]

SELECT [Procedures].[Physician], [Procedures].[CPTCode],
[Procedures].[Description], [Procedures].[Charges]
FROM Procedures
WHERE ((([Procedures].[CPTCode]) Between "11100" And "69979"));

If CPTCode is a Text field - as it appears from the quote marks - then
it's searching *AS TEXT*, sorting the values alphabetically rather
than numerically. The text string "2" is greater than the text string
"11110", just as the text string "B" is greater than the text string
"aardvark".

Try

WHERE (((Val([Procedures].[CPTCode])) BETWEEN 11100 AND 69979))

THe Val() function will return a Number which will be interpreted
numerically.

John W. Vinson[MVP]