UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Export To Excel With A Specific Date Format, Access 2016    
 
   
SemiAuto40
post Apr 23 2019, 04:03 PM
Post#1



Posts: 694
Joined: 3-April 12
From: L.A. (lower Alabama)


When I export the results of a query from a table in Access (which has no special input masking declared) the resulting Excel spreadsheet shows my date as a custom format "dd-mmm-yy" which is not what I want. My users customarily see dates in Excel with a "MM-DD-YY" format. Is there a way to export this in such a way that the formatting can be set to something other than the Excel default date formatting?

Thank you for your knowledge contributions and expertise.
Go to the top of the page
 
June7
post Apr 23 2019, 04:55 PM
Post#2



Posts: 617
Joined: 25-January 16



A date is really saved as a double type value Access date/time field. The dd/mm/yyyy structure you see is default format applied to field.

If you want Excel to show different structure then will have to apply formatting to Excel column.
This post has been edited by June7: Apr 23 2019, 04:56 PM

--------------------
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Doug Steele
post Apr 23 2019, 06:41 PM
Post#3


UtterAccess VIP
Posts: 22,165
Joined: 8-January 07
From: St. Catharines, ON (Canada)


It's always possible to use Automation to apply a format to one or more columns in Excel from Access.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
WildBird
post Apr 23 2019, 07:48 PM
Post#4


UtterAccess VIP
Posts: 3,560
Joined: 19-August 03
From: Auckland, Little Australia


There are a couple of ways to achieve this.

Is it a fixed file you are exporting to? If so, easy. You have a 'template' file and apply a format to the columns you want formatted, and then populate this file.

Another way is use a query and format it in the query - this being said, Excel is a [censored] when it comes to dates! Will try and put its own spin on things. Funnily enough, I always use dd/mmm/yyyy so it is unambiguous.


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
DanielPineault
post Apr 23 2019, 08:15 PM
Post#5


UtterAccess VIP
Posts: 6,671
Joined: 30-June 11



I do all my exporting using Excel automation, this avoids various issues that can occur with other methods and gives you the added bonus of having much, much, much more control; including over the formatting. As such, you could easily use NumberFormat to apply whatever formatting you wish to the data.

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
WildBird
post Apr 25 2019, 08:50 PM
Post#6


UtterAccess VIP
Posts: 3,560
Joined: 19-August 03
From: Auckland, Little Australia


Daniel,

Just looking at your code, and very similar to my own version. What you could do to make it a bit more versatile, is have some extra parameter to use the header and also what line to start it on. I currently need to populate a Excel file but just the data, not the headings, and also it starts on row 4 (I am likely having to populate another workbook and start rows are varied from 4 or 5 to 254, so need a parameter to handle this).

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th June 2019 - 09:30 PM