MS Access SQL query help?

Question

SELECT CHAR_DATE, CHAR_DESTINATION, AC_NUMBER, MOD_NAME, MOD_CHG_MILE FROM CHARTER, MODEL WHERE CHAR_DATE = "06-Feb-06";

When i try running this query it tells me "data type mismatch in criteria expression"

Ive tried for the last 15 minutes changing the code but nothing is helping. I have the dates set in the same setup as the search, i dont know why it isnt working. Any help is appreciated.

tried this and it didnt work

SELECT CHARTER.CHAR_DATE, CHARTER.CHAR_DESTINATION, CHARTER.AC_NUMBER, MODEL.MOD_NAME, MODEL.MOD_CHG_MILE
FROM CHARTER, MODEL WHERE CHARTER.CHAR_DATE ='2/6/2006';

Answer 1

You need to specify a date format so access knows that the date is a date, not a simple string.

Format("06/02/2006,"DD/MM/YYYY")

Also, your query is looking pretty dodgy because you are using the Model table, but no connecting it to the charter table, so the data returned is going to be hideous. You need a

AND charter.id = model.id

type statement in there as well.

Answer 2

Dates have a special delimiter. Use the # sign as the date delimiter

SELECT CHAR_DATE, CHAR_DESTINATION, AC_NUMBER, MOD_NAME, MOD_CHG_MILE FROM CHARTER, MODEL WHERE CHAR_DATE = #06-Feb-06#;