?
Solved

Exporting date fields to Excel

Posted on 2015-02-19
6
Medium Priority
?
136 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
  • 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 2000 total points
ID: 40619098
Then I suggest changing your query to:

SELECT tblLossAllocation.ID, Format([tblLossAllocation].[BillingPeriod],"mm/yy") AS BillingPeriod
FROM tblLossAllocation;
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

864 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