Exporting date fields to Excel

Exporting my report from Access to Excel causes Date Fields to format incorrectly.  This can be resolved by reformatting the date column after exporting, but I would like for that to be automatic.

Attached is a sample Access database to illustrate the issue

Open the report and manually export to Excel.

Windows 7, Access 2010, Excel 2010
ExportDateIssue.accdb
Wayne MarkelOwnerAsked:
Who is Participating?
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
Then I suggest changing your query to:

SELECT tblLossAllocation.ID, Format([tblLossAllocation].[BillingPeriod],"mm/yy") AS BillingPeriod
FROM tblLossAllocation;
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It's confused because of your formatting of mm-yy.

Change the report to dd-mmm-yy, and it will export fine.
0
 
Wayne MarkelOwnerAuthor Commented:
Yes that works to export Day, Month, and Year, but my client wants only the month and year in the exported Excel file since it is a billing period not a billing date.  They can reformat to mm/yy after opening the Excel file, but would like that to be automatic.  Yes it is a cosmetic issue, but that is what they have requested.

Am I misunderstanding your answer?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Wayne MarkelOwnerAuthor Commented:
Immediate response and followup.
0
 
FarWestCommented:
or change the control source of the period to
=Format([BillingPeriod],"mm/yy")
(requires giving the control another name)
0
 
Wayne MarkelOwnerAuthor Commented:
Super!
0
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.