Pelandro Pelandro - 1 month ago 8
SQL Question

Numeric bug on access db

I Have this SQL:

SELECT *
FROM table
WHERE number >= '" + numberFrom + "'
AND number <= '" + numberTo + "'
ORDER BY number DESC


(number is "text" field)

numberFrom
and
numberTo
are a range from "1080" to "2000",
but if i have one record with number "108" the
SELECT
find this record (even if we know that is out of the range).

How i can fix this?

Answer

You are doing alphabetic comparison where for that matter '9' > '19'.
If you want to compare numbers you should use numbers and not text or left pad the text with zeros to a fixed size, e.g. '00019' > '00009'

Comments