Export CSV file date format - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Export CSV file date format

George A Korentis

3 years ago

So I am trying to export my invoices into a .CSV file for easy import into Quick Books. Currently I am running a query and exporting to an .XLSX format from which I open and save as a .CSV file for import into Quick Books. I am trying to skip a step and convert it directly to a .CSV file using:
<CODE>DoCmd.TransferText acExportDelim, , stDocName, stPath & "\" & stFileName & ".csv", True</CODE>

However, no matter what I try, the CSV imports the date format as mm/dd/yyyy 12:00:00 AM

The 12:00:00 does not show up in the .XLSX export nor does it when saving to a.CSV from the excel file. It only occurs while trying to use the DoCmd.TransferText acExportDelim command in VBA.

Note: I have tried using the Export Specifications from the "Export Text extended cut video" but it still produces the 12:00:00 AM

Any help would be greatly appreciated

	Kevin Robertson @Reply 3 years ago
Did you format the Date field in your query? Format

	George A Korentis OP @Reply 3 years ago

	George A Korentis OP @Reply 3 years ago
Thank you for the response Kevin. Yes, I am using the Short Date format within my Query properties for "InvoiceDate", "DueDate", and "ServiceDate". No issues when converting to excel, only to .CSV

	George A Korentis OP @Reply 3 years ago
Kevin, you made me think and went all the way back to the table level. My Data Type, Date/Time is set up for Medium Date not Short Date. I changed them all to Short Date but still get the same results with the time in the .CSV file

	Kevin Robertson @Reply 3 years ago
Use the Format Function to format your date inside the Query.

	Kevin Robertson @Reply 3 years ago

	Kevin Yip @Reply 3 years ago
The Format() function turns a date into text, so Access won't add the time portion. Otherwise, when Access sees a date value, it will add the time portion during export. All dates in Access have a time value. If no time is specified, the time value is zero, which is midnight (12:00:00am), the starting time of every day. That is the reason Access adds 0:00.

	Kevin Robertson @Reply 3 years ago
But Excel will see it as a Date format as shown in my image. I also set the Format to Short Date in the Property Sheet in the Query so Access would see it as a Date. CDate didn't work for the Export (I tried this first).

	George A Korentis OP @Reply 3 years ago
Thanks Kevin Robertson. That worked for me. Tip sent, much appreciated.

	Kevin Robertson @Reply 3 years ago
You're welcome.

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

Next Unseen

New Feature: Comment Live View