Date/Time Values when Exporting CSV File

[Guest]

Hi all,

Again with the questions! Really, I do accomplish things...

So I'm exporting a query to a CSV file. The code I'm using is:
DoCmd.TransferText acExportDelim, , "qryReportRequested", fileName

It works great, except for one problem. Two fields are Date/Time fields.
One is a date (ex format: 9/12/2004), another is time (ex format: 2:34:05
AM). When I open the file the data is displayed as:
date: 9/12/2004 0:00
time: 12/30/1899 14:33:04

Is there a way to force the data in the CSV (therefore excel) to display it
in a similar way as the DB without having to create a new query?

This not terribly important, so if there isn't, I'll live. Just a minor
annoyance I noticed and thought I'd see if there was a way to deal with it.

Thanks,
Jay

 

[Guest]

In your query you can format them the way you need them. for example, lets
say the fields are MyDate and MyTime. In the Field row of your query
builder, instead of putting in the real field name, enter this (of course
change MyDate and MyTime to your actual field names):
For the date field
TheDate: Format(MyDate,"mm/dd/yyyy")

For the time field
TheTime: Format(MyTime,"hh:nn:ss")

If you are not familiar with date/time formatting, since m is for month, n
is used for minutes. If you don't want to show the seconds, leave off the :ss

 

[Guest]

That's what I figured.

Thanks!

In your query you can format them the way you need them. for example, lets
say the fields are MyDate and MyTime. In the Field row of your query
builder, instead of putting in the real field name, enter this (of course
change MyDate and MyTime to your actual field names):
For the date field
TheDate: Format(MyDate,"mm/dd/yyyy")

For the time field
TheTime: Format(MyTime,"hh:nn:ss")

If you are not familiar with date/time formatting, since m is for month, n
is used for minutes. If you don't want to show the seconds, leave off the :ss