Mixed dates - show only complete dates?

[weeblesue]

I have imported an external file into Access 2007. There is a date column that contains different formatted dates. Some are year only, some are MMM-YY, and some are complete DD-MMM-YY. I would like to run a query that isolates only those that are complete, meaning have a date and month.

example data;
1524
1826
JAN-1844
MAR-1987
10-APR-1745


12-NOV-1865

Access has recognized the column as a Text column. If i try to import it as a Date column, there are many, many errors.

However, it is not recognizing the 'year only' entries as years. If I try
CDate([datecol]) on those first 2 rows, the results are:
1099 ---- 1/3/1903
1826 ---- 12/30/1904

Where there is a month and date, Access presumes it's the first of the month, so these entries come out acceptably:
JAN-1844 ---- 1/1/1844
MAR-1987 ----3/1/1987

And of course it's recognizing the full dates as their full correct date (even those from 1400's or 1600's).

I have tried YearX: Year([datecol]),
iif(isnull(month([datecol])),"Yes","No"), and this comes out right (yes's and no's are correct) but when i try to filter as = "Yes", i get "data type mismatch" error!
I've tried coverting the date to Value (Val([datecol]) but had no luck there either.

Any suggestions?

thanks
Susan

[weekend00]

you can run following query for those have only year:
select CDate([datecol]+"-1-1") from table1 where len(trim([datecol]))=4

[weeblesue]

Thanks - but i am trying to exclude those from the query.

But that does give me an idea - use the length of the text string to determine if the date is 'full' or not!

Actually, that works well! If the length of the string is 10 or 11, the date is 'complete'. I can get what i want.

thanks!
Susan

[weekend00]

You are welcome Susan.