How can I take the first 4 characters of a column in SQL and compare it?

Question

I have a Short Text column in Access called ZIP. It has to be a Short Text.

I'm trying to select the first 4 characters of each ZIP and see if it's below a certain number.

MY SQL is:

SELECT *
FROM [WORK]
WHERE LEFT(ZIP, 4) < 300;

I get an error that says "Data type mismatch in criteria expression." which I assume is because the ZIP field is a Short Text and not a number. How would I go about this?

Answer 1

I figured it out, I just needed to put single quotes around the 300.

I ended up with:

SELECT *
FROM [WORK]
WHERE LEFT(ZIP, 4) < '300' or LEFT(ZIP, 4) > '399';

Answer 2

This will work:

SELECT *
FROM [WORK]
WHERE LEFT(ZIP, 3) BETWEEN '300' AND '399';

However, if the zip code always has three digits before the dash, in this case you would only have to check for the very first digit:

SELECT *
FROM [WORK]
WHERE LEFT(ZIP, 1) = '3';

You can also compare numerically:

SELECT *
FROM [WORK]
WHERE VAL(LEFT(ZIP, 3)) BETWEEN 300 AND 399;

and if you have Null values:

SELECT *
FROM [WORK]
WHERE VAL(LEFT(Nz(ZIP), 3)) BETWEEN 300 AND 399;