Hello,

I want to import data into a table, but I'm having a problem. One of the fields being imported is a date field in the format YYMMDD. I've tried importing this field as ALL of the date data types, and Access just erases the field during the import. Is there a way I could import the date as text, then run a query on the field to change the data type to date/time - short date? I want the format (YYMMDD) to stay the same, I just want Access to recognize that the value is a date, and not just text. Oh WOW am I frustrated right now!! Please Help!

Dave

It's all for the thrill of making it work!

What version of Access are you using?



Brian Skelton
Braxis Computer Services Ltd.

Access 2002.

It's all for the thrill of making it work!

What you could do is extract the different parts as you want them. (Being from Australia, I'm going to show you how to do it in dd/mm/yy format)

Right([Field],2) & "/" & Mid([Field],3,2) & "/" & Left([Field],2)

This will convert 010203 to 03/02/01

Which can be inserted to a date field, or imported, the just change the field type from Text to Date.

Of course, it's not that difficult to change it to mm/dd/yy format - but I'm not going to do ALL your work for you :)

Steven

There are 10 kinds of people in the world - those who understand binary - and those with friends

Dave

Here's how you force Access to recognize a 'strangely' formatted date:

1. Choose Get External Data|Import... from the File menu
2. Find the file you wish to import
3. Select Delimited or Fixed Width
4. Click on the Advanced... button
5. You should now see a list of the fields in your file along with Access's best guess as to the data type.
6. Click on the row that contains your date data and ensure its data type is Date/Time.
7. Select 'YMD' from the Date Order combo box.
8. Delete whatever character is in the Date Delimiter box.
9. Untick the Four Digit Years check box.
10. Click the Save As... button and give the import spec. a name. You can then choose this spec next time you import a file with the same format and also pass it as a parameter to the ImportText command.



Brian Skelton
Braxis Computer Services Ltd.

How about just an update query on the imported table with this:

Format(Table.Field,"mm/dd/yyyy")

Kenny Alligood