Export Delimited files removes DateTime formatting?

[klaul]

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?

[orangeCat]

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(BirthDat e,"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

SELECT ancestor.name, Format([birthdate],"DD-MMM-YY") AS bd, ancestor.deathdate, DateDiff("d",[birthdate],[deathdate]) AS ageAtDeath
FROM ancestor
ORDER BY DateDiff("d",[birthdate],[deathdate]) DESC;

The input in the underlying table was:

AgeAtDeathForAc cess
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

[NeoPa]

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!

[klaul]

Thank you very much - orangecat, your solution worked pretty much as you described. Thanks!

[orangeCat]

Thank you very much - orangecat, your solution worked pretty much as you described. Thanks!

Happy to be helpful.