Search code examples
sqlms-accesstype-mismatch

SQL "BETWEEN" command gives data type mismatch

So I have the code

INSERT INTO EMP_1 ( EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE )
SELECT EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_HIREDATE, JOB_CODE
FROM EMPLOYEE
WHERE EMP_NUM BETWEEN 103 AND 109;

and I get the error "Data type mismatch in criteria expression."

I don't get the error when I don't have that WHERE ... BETWEEN line; it adds all 18 lines just as expected. But I only want to add rows for where EMP_NUM is between 103-109. EMP_NUM member in EMP_1 table and EMPLOYEE table are both of the same type: Char(3).

(Also, I would expect EMP_NUM should be a number, not a string, but this is a homework assignment and what do I know)

Solution

  • I'm not familiar with MS-Access, but it looks like it is complaining that EMP_NUM is of type CHAR(3) and you are asking it to be compared to two INT values.

    Try changing the integer values by character ones: WHERE EMP_NUM BETWEEN "103" AND "109";