Importing Dates into Access

[Joe4]

I have a text file which I am importing into Access. The date fields on this file are formatted "mmddyy", so today's date would look like 091306.

In the past, I have always imported the field as a Text field, and used a query to convert the value to a Date field. Is there any way to import the value directly as a date, so I don't need to use a query to convert it?

I don't mind setting up and using Import Specifications, if that helps, or importing into an established table, but want to avoid using a complex VBA routine to do it (I am looking to make this simpler, and using VBA in this case wouldn't be simpler than my query workaround).

Thanks.

 

[MyBoo]

Don't think you can import a text or numeric record into a date field without first adding the date delimiter...ie., "/", "-". etc. If you try doing this, you will get something like Type Conversion Error.

Would seem like less work to request the text file produce a more descriptive date format. This would eliminate the need for a workaround all together.

 

[SydneyGeek]

Dates in Access cause rapid hair loss...

I reckon you're best off sticking with the current plan -- import as text, and run an update query.
I guess you could assign those steps to a button if you want to simplify the process for other users, but having a consistent format like you mentioned is your best bet at the moment.

Denis

 

[Joe4]

Thanks for the replies. The current method works well, I am just always looking to simplify things, if possible. Looks like my current method is the way to go (MyBoo, the format of the incoming file is something we have no control over -- believe me, that's always my first option!)

 

[wongm003]

Try setting up an Import Specification and use a Date/Time Data Type and delete the Date Delimiter... worked in Access 2003.

 

[VicRauch]

WOW!
I just tried the import specs and it works! It's not that I thought it wouldn't, I just wanted to see it.

Great job wongm003!
thanks,

 

[SydneyGeek]

Thanks wongm003,

Great tip!

I wouldn't have thought about that because I have had so much grief with date formats -- and Access seems to default to US when converting from text -- but definitely one to remember.

Denis

 

[Joe4]

BRAVO wongm003!

I tested it out (I have Access 2000), and it works great!

Thanks!