Hi,

I currently using a macro in Access 97 to export a large table to a CSV
file.  Im using Transer Text Action - with Transfer type "Export
Delimited".

Within the original table I have a date field which is in dd/mm/yyyy
format.  When I run the macro the resulting CSV file has altered the
dates to dd/mm/yy hh:mm:ss format.  I have thought about running some VB
to open the csv file and change the date back but due to the size of the
file it would take too long (or crash my PC!)

If anyone out there is a Macro Guru - pleas help............

TIA

Tim
This e-mail transmission is strictly confidential and intended solely 
for the person or organisation to who it is addressed.  It may contain 
privileged and confidential information and if you are not the 
intended recipient, you must not copy, distribute or take any action 
in reliance on it.  
If you have received this email in error, please notify us as soon as 
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however, 
Swansea NHS Trust accept no responsibility for infection caused by 
any virus received on the recipients system.

sounds like the problem is the format of the cells in excel.
goto excel, and where ever the cells are to receive the date from
access are.   choose them, and make sure the date format is set
for the same format as the format of the date fields in access.
you may have to choose custom format and make sure you just use the
format for dd/mm/yyyy and do not include the time portion.

later,
bware

-----Original Message-----
From: Tim Maher [mailto:tim.maher@s...]
Sent: Friday, January 24, 2003 3:07 AM
To: Access
Subject: [access] Macro to export to CSV - Formatting Date Field????????


Hi,

I currently using a macro in Access 97 to export a large table to a CSV
file.  Im using Transer Text Action - with Transfer type "Export
Delimited".

Within the original table I have a date field which is in dd/mm/yyyy
format.  When I run the macro the resulting CSV file has altered the
dates to dd/mm/yy hh:mm:ss format.  I have thought about running some VB
to open the csv file and change the date back but due to the size of the
file it would take too long (or crash my PC!)

If anyone out there is a Macro Guru - pleas help............

TIA

Tim
This e-mail transmission is strictly confidential and intended solely 
for the person or organisation to who it is addressed.  It may contain 
privileged and confidential information and if you are not the 
intended recipient, you must not copy, distribute or take any action 
in reliance on it.  
If you have received this email in error, please notify us as soon as 
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however, 
Swansea NHS Trust accept no responsibility for infection caused by 
any virus received on the recipients system.

ok then, in excel choose the date column header the will select
everthing in that column, and then change the format. which will
maintain your wanted format for the current items and any new items
that are imported in the future,, good luck.

later,
bware

-----Original Message-----
From: Tim Maher [mailto:tim.maher@s...]
Sent: Friday, January 24, 2003 4:10 AM
To: bwarehouse@y...
Subject: [access] RE: Macro to export to CSV - Formatting
DateField????????


Thanks for the reply,

I thought about opening the excel file and manually changing the
columns where the dates are wrong.  But CSV file I am exporting will
hold over 100,000 rows - I understand that this is too much for Exel
97???????????

TIA

Tim

>>> bwarehouse@y... 01/24/03 10:59am >>>
sounds like the problem is the format of the cells in excel.
goto excel, and where ever the cells are to receive the date from
access are.   choose them, and make sure the date format is set
for the same format as the format of the date fields in access.
you may have to choose custom format and make sure you just use the
format for dd/mm/yyyy and do not include the time portion.

later,
bware

-----Original Message-----
From: Tim Maher [mailto:tim.maher@s...] 
Sent: Friday, January 24, 2003 3:07 AM
To: Access
Subject: [access] Macro to export to CSV - Formatting Date
Field????????


Hi,

I currently using a macro in Access 97 to export a large table to a
CSV
file.  Im using Transer Text Action - with Transfer type "Export
Delimited".

Within the original table I have a date field which is in dd/mm/yyyy
format.  When I run the macro the resulting CSV file has altered the
dates to dd/mm/yy hh:mm:ss format.  I have thought about running some
VB
to open the csv file and change the date back but due to the size of
the
file it would take too long (or crash my PC!)

If anyone out there is a Macro Guru - pleas help............

TIA

Tim
This e-mail transmission is strictly confidential and intended solely 
for the person or organisation to who it is addressed.  It may contain

privileged and confidential information and if you are not the 
intended recipient, you must not copy, distribute or take any action 
in reliance on it.  
If you have received this email in error, please notify us as soon as 
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however, 
Swansea NHS Trust accept no responsibility for infection caused by 
any virus received on the recipients system.






This e-mail transmission is strictly confidential and intended solely 
for the person or organisation to who it is addressed.  It may contain 
privileged and confidential information and if you are not the 
intended recipient, you must not copy, distribute or take any action 
in reliance on it.  
If you have received this email in error, please notify us as soon as 
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however, 
Swansea NHS Trust accept no responsibility for infection caused by 
any virus received on the recipients system.

Base the transfer action on a query instead of the table.  In the query,
format the date field - format(MyDateField,"mm/dd/yyyy").  This will 
resolve
your problem.

Here's what the SQL statement in the query looks like:

SELECT Table1.Salary, Format([Date],"mm/dd/yyyy") AS MyDate
FROM Table1;




John V. Ruff - The Eternal Optimist :-)
Always Looking For Contract Opportunities

www.noclassroom.com
Live software training
Right over the Internet

Home:  xxx.xxx.xxxx
Cell: 253.307/2947
9306 Farwest Dr SW
Lakewood, WA 98498

"Commit to the Lord whatever you do,
	and your plans will succeed." Proverbs 16:3



-----Original Message-----
From: Tim Maher [mailto:tim.maher@s...]
Sent: Friday, January 24, 2003 2:07 AM
To: Access
Subject: [access] Macro to export to CSV - Formatting Date Field????????


Hi,

I currently using a macro in Access 97 to export a large table to a CSV
file.  Im using Transer Text Action - with Transfer type "Export 
Delimited".

Within the original table I have a date field which is in dd/mm/yyyy 
format.
When I run the macro the resulting CSV file has altered the dates to
dd/mm/yy hh:mm:ss format.  I have thought about running some VB to open 
the
csv file and change the date back but due to the size of the file it 
would
take too long (or crash my PC!)

If anyone out there is a Macro Guru - pleas help............

TIA

Tim
This e-mail transmission is strictly confidential and intended solely
for the person or organisation to who it is addressed.  It may contain
privileged and confidential information and if you are not the
intended recipient, you must not copy, distribute or take any action
in reliance on it. 
If you have received this email in error, please notify us as soon as
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however,
Swansea NHS Trust accept no responsibility for infection caused by
any virus received on the recipients system.

> Hi,

I currently using a macro in Access 97 to export a large table to a CSV
file.  Im using Transer Text Action - with Transfer type "Export
Delimited".

Within the original table I have a date field which is in dd/mm/yyyy
format.  When I run the macro the resulting CSV file has altered the
dates to dd/mm/yy hh:mm:ss format.  I have thought about running some VB
to open the csv file and change the date back but due to the size of the
file it would take too long (or crash my PC!)

If anyone out there is a Macro Guru - pleas help............

TIA

Tim
This e-mail transmission is strictly confidential and intended solely 
for the person or organisation to who it is addressed.  It may contain 
privileged and confidential information and if you are not the 
intended recipient, you must not copy, distribute or take any action 
in reliance on it.  
If you have received this email in error, please notify us as soon as 
possible and delete it.
This e-mail has been scanned using Anti-Virus software, however, 
Swansea NHS Trust accept no responsibility for infection caused by 
any virus received on the recipients system.

> Hi Tim:
   I have found the best way around this issue is to make the date a text 
field and then export it..
Bill