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