Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 24th, 2004, 05:09 PM
Registered User
Join Date: Feb 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default transfertext and dates

I am exporting some long dates with the field format being defined within the query. In the query results it shows Tuesday, February 24, 2004. When the data is exported to a tab delimited file the date shows as 2/24/2004 0:00:00. How do I get it in the correct format???

Old February 24th, 2004, 07:07 PM
Authorized User
Join Date: Feb 2004
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts

I was never happy with transfer text and the assumptions it made in regard to formatting data. If you don't mind writing a bit of code you can use a bit of old fashioned Basic (and VB/A) code to control the process. You could use the file system object as well, but that requires a reference to the Scripting libraries which many companies refuse to enable. The conversion of text file data to access tables is even worse as no data validation can take place in the import. If you have embedded tabs in the data you are ouputting to a tab delimited file with transferdatabase, you can't strip them out and the exported data ends up in incorrect columns often resulting in type conversion errors and the whole shooting match fails. Using file i/o, you have complete control of all aspects of the format of each line and have the ability to log missed records and continue despite errors. Have a look at:


click the link to 'My Downloads'

and then open the link:

'File I/O in VB'

There are 3 examples. The 2nd is outputting from recordset to a semi colon delimited file. Change the line:

strOutput = rst!Field1 & ";" & rst!Field2 & ";" & rst!Field3


strOutput = rst!Field1 & vbTab & Format$(rst!Field2, "dddd, mmmm dd, yyyy") & vbTab & rst!Field3

in the case where the 2nd field of the recordset is the formatted date.

Jürgen Welz
Edmonton AB Canada
Old February 24th, 2004, 09:06 PM
Registered User
Join Date: Feb 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks, I'll try it tomorrow.

Similar Threads
Thread Thread Starter Forum Replies Last Post
TransferText Decimals Issue Pavesa Access 0 April 23rd, 2008 06:48 PM
TransferText Ignores Index in Import Specification nancyb Access VBA 1 September 29th, 2006 05:21 PM
Importing HTML using TransferText AvGuy Access VBA 0 April 19th, 2006 02:19 PM
doCmd.TransferText d_olivastro VB Databases Basics 1 March 31st, 2006 12:06 AM
TransferText method sc-prgmr Access VBA 0 October 12th, 2005 09:46 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.