error of change a year date format on MS Access 2012 by running a SQL query

Question

I am working on Access 2012 on win 7. In a table, I need to change a year date format from '1-Jan-06' to 2006 and from '1-Jan-90' to 1990. I only need year.

This is my query

SELECT  *, 
CASE 
   WHEN CAST(right(year_date,2) , INT) <= 12 
   THEN 2000 + CAST(right(year_date,2) , INT) 
   ELSE 1900 + CAST(right(year_date,2) , INT) 
 END
FROM my_table;

I got error:

syntax error (missing operator) in the query expression of 'CASE -- END'.

Answer 1

Access SQL does not support CASE WHEN or CAST.

In Access, you can use IIf() and CInt() to do what you intended with CASE WHEN and CAST.

SELECT 
    IIf(CInt(Right(year_date, 2)) <= 12, 2000, 1900)
        + CInt(Right(year_date, 2))
FROM my_table;