How to convert a text date format, for example mmm-yy, to a date in a request in MS Access?

Question

I have a text file with the format mmm-yy (all months - abbreviation 3 letters, for example, January, February, March)

May-31 

This means that "May 1931."

If I use the following in a query:

 CDate([BIRTHDT]) 

I get May 31, 2012, not May 1, 1931. Other lines that have years that later, for example, May-32, give the desired result on May 1, 1932. Obviously, this is due to ms-access text to date conversion. Validation mmm-dd takes precedence over the likely less common mmm-yy format, but yields unexpected results.

So for some reason I need to extract a month from the first three characters, a year from the last two digits and combine them. Ideally, I would like to do this in MS Access SQL.

Answer 1

"I need to extract a month from the first three characters, a year from the last two digits and combine them. Ideally, I would like to do this in MS Access SQL."

Here is the session in the Immediate window.

 BIRTHDT = "May-31" ? BIRTHDT May-31 ? Left(BIRTHDT,3) & "-1-" & Right(BIRTHDT,2) May-1-31 ? CDate(Left(BIRTHDT,3) & "-1-" & Right(BIRTHDT,2)) 5/1/1931 

So you can use this expression in a query.

 SELECT CDate(Left(BIRTHDT,3) & "-1-" & Right(BIRTHDT,2)) FROM YourTable;