You are confusing
text "3001" and "4000" with
numbers 3001 and 4000. Your mind is automatically converting the TEXT "3001" and "4000" to numbers.
-> With text, access compares characters from left to right. <- (we're talking about comparing ASCII values from left to right)
You get "400" because ACCESS compares the first character (ASCII value) in [Price] with the first character (ASCII value) in "3001" and with the first character (ASCII value) in "4000".
Is the "4" (in the 400") is between "3" and "4" inclusive?
Yes
Then the 2nd character is compared. So next is the character "0" (from Price). Is the 2nd character "0" (in the 400") is between "0" (from "3001") and "0" (from "4000") inclusive?
Yes
Then the 3rd character is compared. So next is the character "0" (from Price). Is the 3rd character "0" (in the 400") between "0" (from "3001") and "0" (from "4000") inclusive?
Yes
So "400" meets the criteria, even though we
know that 400 should not be returned.
Lets say you have a table designed like this
and enter this data in each field
If you then create a query sorted by
PriceNumber, you will see
but if you change sorted by
PriceText, you will get
Why is the
PriceText not sorted "correctly"?
Guess what happens if the query is
Code:
SELECT PriceText
FROM tblFilterTest
WHERE (((tblFilterTest.PriceText) Between "1001" And "3000"));
(you only get 20)
"Between" is used for number types and datetime type fields (datetime is actually a number - double).
I don't use macros (never even wrote a macro), so I don't know what type of formula would work for the criteria.