Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-11-2012, 08:27 PM   #1
RichO
Registered Yoozer
 
RichO's Avatar
 
Join Date: Jan 2004
Location: Wisconsin, USA
Posts: 1,036
Thanks: 14
Thanked 2 Times in 2 Posts
RichO is on a distinguished road
Date format with TransferText

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

__________________
Access 2010, Windows 7
RichO is offline   Reply With Quote
Old 02-11-2012, 09:19 PM   #2
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Date format with TransferText

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
vbaInet is offline   Reply With Quote
The Following User Says Thank You to vbaInet For This Useful Post:
RichO (02-12-2012)
Old 02-12-2012, 08:30 AM   #3
RichO
Registered Yoozer
 
RichO's Avatar
 
Join Date: Jan 2004
Location: Wisconsin, USA
Posts: 1,036
Thanks: 14
Thanked 2 Times in 2 Posts
RichO is on a distinguished road
Re: Date format with TransferText

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!

__________________
Access 2010, Windows 7
RichO is offline   Reply With Quote
Old 02-12-2012, 08:37 AM   #4
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Date format with TransferText

Good to hear.
Quote:
Originally Posted by RichO View Post
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.
Quote:
Originally Posted by RichO View Post
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.
vbaInet is offline   Reply With Quote
Old 02-12-2012, 10:37 AM   #5
Kiwiman
Registered User
 
Kiwiman's Avatar
 
Join Date: Apr 2008
Location: Bath, UK
Posts: 799
Thanks: 8
Thanked 57 Times in 57 Posts
Kiwiman will become famous soon enough Kiwiman will become famous soon enough
Send a message via ICQ to Kiwiman
Re: Date format with TransferText

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.
__________________

__________________
HTH's
Take It Easy
Pete
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Man who keep feet firmly on ground have trouble putting on pants.
Kiwiman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
when I use TransferText from Access table to .txt file, data format is changed lhanes72 Macros 9 01-20-2011 10:57 AM
How to convert text format date to date format mm/dd/yyyy sushmitha Queries 1 08-21-2008 01:58 PM
Convert Excel Serial Date format to Access Date format? DocNice General 3 03-15-2005 05:58 AM
Convert Excel Serial Date format to Access Date format? DocNice General 2 03-14-2005 02:32 PM
Match date (in date format) to date (in text format) - HELP!! Clare Queries 5 02-26-2003 07:29 AM




All times are GMT -8. The time now is 07:50 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World