Solved

Exporting date fields to Excel

Posted on 2015-02-19
6
123 Views
Last Modified: 2015-02-19
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
0
Comment
Question by:Wayne Markel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40619048
It's confused because of your formatting of mm-yy.

Change the report to dd-mmm-yy, and it will export fine.
0
 

Author Comment

by:Wayne Markel
ID: 40619078
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
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40619098
Then I suggest changing your query to:

SELECT tblLossAllocation.ID, Format([tblLossAllocation].[BillingPeriod],"mm/yy") AS BillingPeriod
FROM tblLossAllocation;
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Closing Comment

by:Wayne Markel
ID: 40619121
Immediate response and followup.
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40619160
or change the control source of the period to
=Format([BillingPeriod],"mm/yy")
(requires giving the control another name)
0
 

Author Comment

by:Wayne Markel
ID: 40619172
Super!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question