Import a date like JAN-07 in MMM-YY in access

bae14

I have a file I'm trying to import into Access that has dates in it that are in the form MMM-YY (JAN-07) and Access isn't letting me import it in as a date. It says type conversion error. I need this field to come in as a date because I want to prompt a user for a date range for them to get a list of data. For instance, Enter a date range desired and they will put in JAN-07 and then DEC-07. Thanks for any help you can give.

Jan 29 '09 #1

missinglinq

Sorry, but JAN-07 and DEC-07 are simply not dates, and Access is not going to recognize them as such. My guess is that you're going to have to import them as Text, which they are, and then, post-importation, manipulate the data in some manner, possibly parsing the data and adding an arbitrary day of the month to each one.

Welcome to Bytes!

Linq ;0)>

Jan 29 '09 #2

beacon

Yeah, the worst part about using JAN-07 for a date and then using it to find a range of dates is that there isn't any indicator of whether or not JAN-07 should "JAN 1, 2007", "JAN 31, 2007", "JAN 1, 1907", or "JAN 31, 1907".

With four possibilities for each one date, I'm almost positive it would be nearly impossible to find some kind of date range using an expression alone. If you could do it, the expression would be extremely long and complicated.

It could be done in VBA, but you still have the problem of determining if the parameters you use for the date ranges should be the 1st of the month or the last day of the month AND you have to calculate what the last day of the month is since January will have 31 days and February will have 28 (sometimes 29) days.

Jan 29 '09 #3

bae14

Thanks a lot for both of your help. I may try putting in an arbitrary day and see if I can work it from that angle. Thanks SO MUCH!

Jan 30 '09 #4

beacon

If you have a day, you could write an expression using CDate to convert the string to a date. You can actually use CDate with what you have, but Access is going to think that the last two digits are the day and will attach the current year to the date (giving you 01/07/2009).

I guess if you put in an arbitrary day, you could use CDate and then format it to display JAN 07 (using Format(CDate[YourDateField], "MMM yy")).

Jan 30 '09 #5