Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 26th, 2003, 01:01 PM
Registered User
 
Join Date: Jun 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to daver676
Default Import Date Format

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!
 
Old June 27th, 2003, 01:35 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What version of Access are you using?



Brian Skelton
Braxis Computer Services Ltd.
 
Old June 27th, 2003, 07:10 AM
Registered User
 
Join Date: Jun 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to daver676
Default

Access 2002.

It's all for the thrill of making it work!
 
Old June 27th, 2003, 07:51 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 28th, 2003, 05:44 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old June 28th, 2003, 08:06 AM
Authorized User
 
Join Date: Jun 2003
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

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

Kenny Alligood





Similar Threads
Thread Thread Starter Forum Replies Last Post
date format differs, need to force format somehow patricolsson ASP.NET 2.0 Basics 1 December 3rd, 2009 12:53 AM
Convert British format date to American format? fyr PHP How-To 0 December 19th, 2007 03:17 PM
Format loss on import needelp Access VBA 1 September 11th, 2006 07:34 AM
date format differs, need to force format somehow patricolsson HTML Code Clinic 2 January 12th, 2006 05:55 AM
DTS Import ( Date string to Date field) gfowajuh SQL Server 2000 1 September 30th, 2003 06:28 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.