Results 1 to 9 of 9
  1. #1
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136

    Weird date re-format when exporting to Excel


    Hi all,
    I have a pretty simple report with dates in it. In the report I have a date selected e.g. 2/1/2023 but displayed as Feb-23. When I export the report to Excel it changes the date to 2/23/2022 and displays it as Feb-22. Any idea how or why that's happening?

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    How are you performing the export?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Quote Originally Posted by Minty View Post
    How are you performing the export?
    Via a macro and the ExportWithFormatting function:
    Click image for larger version. 

Name:	Capture.JPG 
Views:	19 
Size:	20.4 KB 
ID:	48623

    The date field is formatted mmm-yy in the report. If I change that formatting to mm-dd-yyyy it exports fine. I don't want to do that though.


  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The date field is formatted mmm-yy in the report.
    how are you formatting it in the report? using the format function, either in the recordsource or in a control? or the format property of the control. The former will give you the result you are getting, the latter should get you the result you want

  5. #5
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Quote Originally Posted by CJ_London View Post
    how are you formatting it in the report? using the format function, either in the recordsource or in a control? or the format property of the control. The former will give you the result you are getting, the latter should get you the result you want
    I'm formatting it via the properties of the control. The record source is formatted mm-dd-yyyy. Is that the issue?

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    In that case it is probably export with formatting is the issue. What happens if you export without formatting?

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I tested export of report with the wizard and Without Formatting is not offered as an option. The With Formatting checkbox is checked and greyed out. It becomes available when exporting a table or query.

    I don't see Without Formatting in macro options either.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't use macro's (limited options) but in vba you can use docmd.outputTo. See this link about how to use it https://access-excel.tips/access-vba...tputto-method/

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    OutputTo also exports formatted date.

    Side Note: I use Access 2010 and apparently acFormatXLS is available but acFormatXLSX is not.


    However, in each case, the full date is actually exported. Just have to change the Excel cell format to full date structure.


    Why not export from table or query instead of report?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Getting the right date format when exporting a file
    By MTSPEER in forum Programming
    Replies: 16
    Last Post: 08-31-2017, 09:59 AM
  2. Customize Date Report when exporting the data to excel format
    By fluffyvampirekitten in forum Access
    Replies: 1
    Last Post: 08-19-2015, 03:33 AM
  3. Replies: 1
    Last Post: 09-06-2012, 12:33 PM
  4. Exporting a query to a specific Excel format
    By Ronald Mcdonald in forum Import/Export Data
    Replies: 3
    Last Post: 04-27-2012, 10:29 AM
  5. Date format exporting to txt
    By timpepu in forum Access
    Replies: 1
    Last Post: 04-20-2010, 08:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums