Short Date Format isn't Really

[maduko]

I'm working on a project that involves exporting a table from Access to a text file that will be chewed on by a legacy app. I keep getting data mismatch errors and finally tracked it to the date field. It wants to see:
2/31/2006

The table with the original data has the date stored as Long Date with the time. My queries and table are all formatted as Short Date, and display as such. But when you export a text file the dates are long again- they have the time:
2/31/2006 12:31:55

How do I make them actually short?

 

[PHV]

Export a formatting query instead of the table:
SELECT ..., Format([DateTime field],'m/dd/yyyy') AS myShortDate, ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Dates (or more properly DateTimes) in access are not stored as long, short or any other variant of the formatting convention. They are stored as numeric doubles with the date portion before the decimal place and the time after it as a fraction.

Exporting is using your regional settings (Long or Short) to convert the internal representation to a date. You can either change the regional settings or explicitly convert the internal representation (i.e. a number) to the format you want using a "Format" statement.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[maduko]

Ouch.

That sounds like a lot of work but I'll consider it.

 

[maduko]

FORMAT!

Excellent, thank you.