467,905 Members | 1,858 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,905 developers. It's quick & easy.

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

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
  • viewed: 2418
Share:
4 Replies
missinglinq
Expert 2GB
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
512MB
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
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
512MB
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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by jean.rossier | last post: by
1 post views Thread by David | last post: by
7 posts views Thread by Mick White | last post: by
16 posts views Thread by Atley | last post: by
26 posts views Thread by jshanman | last post: by
6 posts views Thread by CsharpGuy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.