My Select query has two tables -- MASTER and TCDS_Data. The two tables are left-joined on MASTER.[MFR MDL CODE] = TCDS_Data.CODE. The query has five fields -- MASTER.[N-NUMBER], MASTER.[MFR MDL CODE], MASTER.[SERIAL NUMBER], TCDS_Data.tcdsSerialStart1, and TCDS_Data.tcdsSerialEnd1. The Criteria in the SERIAL NUMBER field is "Between [tcdsSerialStart1] And [tcdsSerialEnd1]" (without the quotes), so the query seeks to return all records where SERIAL NUMBER falls between tcdsSerialStart1 and tcdsSerialEnd1 for a given MFR MODEL CODE. All of the fields in all tables are Short Text formatted.
Here's the problem: For TCDS_Data.CODE= "1151410", the serial number range is CE-1 to CE-179. The query should return 116 records from MASTER, but it is only returning the records where the serial number is CE-1* -- it won't return any records where the first number following "CE-" is 2, 3, 4, 5, 6, 7, 8, or 9. In other words, it won't return the records matching serial numbers CE-2 or CE-90 even though CE-2 and CE-90 fall between CE-1 and CE-179. Access thinks that CE-2 and CE-90 are bigger than CE-179 since "2" and "9" are bigger than "1" in the first character after "CE-". Access is treating the alphanumeric more like a number instead of text, even though the field is formatted as Short Text and the data is, by definition, alphanumeric.
How do I get Access to treat the data as text and evaluate the entire field, versus treating it as a number and evaluating just the first numeric digit? In other words, how do I get Access to acknowledge that CE-2 falls between CE-1 and CE-179, and not outside of it?
Here is the query:
SELECT MASTER.[N-NUMBER], MASTER.[MFR MDL CODE], MASTER.[SERIAL NUMBER], TCDS_Data.tcdsSerialStart1, TCDS_Data.tcdsSerialEnd1
FROM MASTER LEFT JOIN TCDS_Data ON MASTER.[MFR MDL CODE] = TCDS_Data.CODE
WHERE MASTER.[SERIAL NUMBER] Between [tcdsSerialStart1] And [tcdsSerialEnd1]
ORDER BY MASTER.[N-NUMBER];