Importing Dates into Access

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,426
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,426
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 8, 2005
Messages
237

ADVERTISEMENT

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

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,426
Office Version
  1. 365
Platform
  1. Windows
BRAVO wongm003!

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

Thanks!
:biggrin:
 

Forum statistics

Threads
1,137,339
Messages
5,680,910
Members
419,940
Latest member
WilliamPorter

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top