Access To CSV Incorrect Time - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Access To CSV Incorrect Time

Teeka Leone

4 years ago

Hi! I've been struggling with this for months. I've been through all of the date/time courses here and all over the internet and I'm not finding anything.
In my SQL Server table the [SchedTime] field datatype = datetime
Looking at the table in SQL Server the data looks like this:
1899-12-30 17:30:00.000.  (not desired format)
In the Access query that fills the form Adam helped me create a column alias using Time Value where the SchedTime looks like: 5:30 PM (WHICH IS PERFECT)
When I Export the form data to CSV even the aliased column using timevalue shows as  [12/30/1899 17:30:00]
How can I handle this in VBA so that the export is formatted correctly (5:30 PM)?  In my project, a third party APP needs to consume the CSV file and map appropriately to its time field.  Any thoughts on how I can force this formatting into the CSV?

	Kevin Yip @Reply 4 years ago
You need to output the time as a string, so the CSV will get exactly what the string looks like. Try exporting a query like the one below to CSV: SELECT Format([timefield1],"h:nn am/pm") AS timefieldstr FROM Table1;

	Scott Axton @Reply 4 years ago
Yep what Kevin said. See the Format Function page in the Glossary for more info.

	Teeka Leone OP @Reply 4 years ago
Thank you Kevin and Scott. That didn't work. Access and/or SQL completely ignored the formatting statement. BUT, Adam (Yoda) was able to help! it was a two step process: 1. Convert the field in the recordset to string then change the datatype in the temp table for the csv dump to text and Voila!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View