Export date field via a query to an Excel file

I am exporting a query result to an Excel file.  The query contains a date field.  The date field is Short Date by default.  But when for example 3/14/2017 gets exported to the Excel file it looks like 3/14/2017  12:00:00 AM

Why is this happening?

The export VBS code is:

DoCmd.TransferText acExportDelim, , "qryRecords", strCSV & Environ("userProfile") & "\Desktop\" & "Records " & Format(Now, "mm-dd-yyyy_hh-nn") & ".csv", True

Open in new window

SteveL13Asked:
Who is Participating?
 
Paul Cook-GilesConnect With a Mentor Access/VBA/SQL Database DeveloperCommented:
Even though the table is formatted for ShortDate, the time value is still part of the entry;  it's just 0.  If you format the field in the query you're exporting like this:  Format(DateField, "M/D/YYYY")  as FormattedFieldName   the value exported should be just the date.  (Note that "M/D/YYYY" will return 1/3/2018 or 11/15/2018;  "MM/DD/YYYY" will return 01/03/2018 or 11/15/2018.  :)
1
 
Fabrice LambertConnect With a Mentor Fabrice LambertCommented:
Hi,

MS Access export the date value, not the format. Then MS Excel give it a default format.

If you want a specific format, convet to string by using the format() function in your query.
0
 
Gustav BrockConnect With a Mentor CIOCommented:
All you need is to apply a format (like m/d/yyyy ) to the column in the worksheet holding the date values.

/gustav
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.