By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Export Delimited files removes DateTime formatting?

P: 6
I'm hoping that this is going to be an easy one to solve, but I've been trawling google and have had no luck at all!
I'm using Access 2003, and am trying to export the contents of a table into a pipe delimited text file using a macro with a pre-set export specification.
The file exports fine, but one of my columns is a date column. The formatting in the table is dd-mmm-yy, but when I export it becomes dd/mm/yy 00:00:00.
I'm trying to firstly remove the time from the file, and also to get the date format back to dd-mmm-yy. Can anyone give me some pointers?
Jan 19 '09 #1
Share this Question
Share on Google+
4 Replies


P: 83
I think that when you export delimited files they become text files. So, Date formatting is no longer applicable.

I also think you should explicitly format your access Date fields
eg BD: Format(BirthDate,"DD-MMM-YY")
and then do the export with pipe delimiters.

If you don't do the Format, the date will be a DD/MM/YY date with time (see the output file)

I tried this with the following query, SQL is

Expand|Select|Wrap|Line Numbers
  1. SELECT ancestor.name, Format([birthdate],"DD-MMM-YY") AS bd, ancestor.deathdate, DateDiff("d",[birthdate],[deathdate]) AS ageAtDeath
  2. FROM ancestor
  3. ORDER BY DateDiff("d",[birthdate],[deathdate]) DESC;
  4.  
The input in the underlying table was:

AgeAtDeathForAccess
bob,10/31/1922,3/13/1976,19492
sam,11/1/1922,3/12/1976,19490
jim,3/22/1944,9/17/1964,7484
]ken,9/9/1999,2/7/2006,2343

The output was :

"bob"|"31-Oct-22"|3/13/76 0:00:00|19492
"sam"|"01-Nov-22"|3/12/76 0:00:00|19490
"jim"|"22-Mar-44"|9/17/64 0:00:00|7484
"ken"|"09-Sep-99"|2/7/06 0:00:00|2343
Jan 20 '09 #2

NeoPa
Expert Mod 15k+
P: 31,186
As orangeCat says (pretty close anyway) you need to create a query based on the table and export the query (using Format([Field],'dd-mmmm-yy')).

This is because exporting does not format the data. A Date/Time field is simply a real number which, when displayed, is treated as a Date/Time value and formatted as such.

Welcome to Bytes!
Jan 20 '09 #3

P: 6
Thank you very much - orangecat, your solution worked pretty much as you described. Thanks!
Jan 20 '09 #4

P: 83
@klaul
Happy to be helpful.
Jan 20 '09 #5

Post your reply

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