Date format with TransferText

[RichO]

Hello all,

I am using this code:

DoCmd.TransferText acExportDelim, "ExportScheduleCSV", "tb_WebScheduleCSV", strPath & "Band-schedules.csv"

to transfer table data to a CSV file. This works just fine but in the process, the date format goes from 02/11/2012 to 2/11/2012 0:00.

The table is formatted for mm/dd/yyyy but the exported CSV file ends up with mm/dd/yyyy hh:nn.

Any ideas how to fix this? The export specs don't have an option for the needed formatting.

Any help would be appreciated. Thanks

 

[vbaInet]

Your date in the table has a time part to it or the column in your spreadsheet isn't formatted to exlude the Time part. Which of these is the case?

Do you have a format set in the Format property of the field in the table? If you do then that format will not apply when you export it unless you tick the Export data with formatting and layout checkbox in the Import/Export dialog box.

If you don't want all the fancy formatting exported then you have two options:

1. Create a query based on your table, use the Format() function on that field and export this query.

OR

2. Format that column in the spreadsheet

 

[RichO]

Yes, I do have the format property set to mm/dd/yyyy.

I am appending the data to the table using VBA and I did try using Format$ on the date field when appending which made no difference so it had to be happening during the export to CSV.

However, your advice to query the table using Format$ while exporting did the trick.

Thanks!

 

[vbaInet]

Good to hear.

Yes, I do have the format property set to mm/dd/yyyy.

Just so you are aware, the Format property doesn't affect the raw data, it only affects the presentation of data so when you are exporting data it exports the raw data unless the Export data with formatting and layout was ticked. Now you could have also used this option if you re-ran your Export Spec, ticked this option and saved the spec. But it will come with some formatting that you may not want.

I am appending the data to the table using VBA and I did try using Format$ on the date field when appending which made no difference so it had to be happening during the export to CSV.

Oh yes CSV, that won't have worked.

 

[Kiwiman]

Howzit

I had a similar problem when doing a migration job for Great Plains - all the dates were coming across as dd/mm/yy hh:mm - but I only wanted dd/mm/yy and there was no time factor in the date.


I got round this by changing the datatype in the specification I was using to export the data, from DateTime (the releveant date field datatype) to Text and formatting it in the required format - in my case DMY.

This exported the values as text excluding the 00:00 - 25/12/09. When i used the export to import into SQL server and other access databases, excel etc, they came in as dates.
__________________