Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    matey56 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    40

    Exporting to Excel - not recognizing dates


    Hi,
    I have a report based on a query that uses a few calculated fields using dates. When I export the report to Excel, these fields/columns are not recognized as dates by Excel. I can set the format to Short Date in the query but not the report. The format option in the property sheet is blank. All the fields that are not formulas are set to Short Date. How do I fix this?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,831
    are you using
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query1", sFile, True

    I export dates all the time just fine.
    you can set format in the report. Set textbox format to shortdate. But you shouldn't need a report for Transferspreasheet.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,309
    I assume you are exporting the report using Docmd.OutputTo. If you want those fields to show up in Excel as a formatted date and not its value (i.e. 43191 for 1\04\2018) open the report in design view and replace the control source of those controls with =Format("[YourCalculatedDateField]","DD-MMM-YYYY") or whatever format you want to use.
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    matey56 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    40
    Quote Originally Posted by ranman256 View Post
    are you using
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query1", sFile, True

    I export dates all the time just fine.
    you can set format in the report. Set textbox format to shortdate. But you shouldn't need a report for Transferspreasheet.
    I'm actually using a ExportWithFormatting macro to export to Excel.

  5. #5
    matey56 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    40
    Quote Originally Posted by Gicu View Post
    I assume you are exporting the report using Docmd.OutputTo. If you want those fields to show up in Excel as a formatted date and not its value (i.e. 43191 for 1\04\2018) open the report in design view and replace the control source of those controls with =Format("[YourCalculatedDateField]","DD-MMM-YYYY") or whatever format you want to use.
    Cheers,
    That doesn't work. It just puts the name of the calculated control in the report. Here's what I put for the control source for the control Final_Dec_Date_New_or_Reprint: =Format("[Final_Dec_Date_New_or_Reprint]","dd/mm/yyyy")

    What am I doing wrong?

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,715
    You can't call the result the same as another field or the field in the calculation (you will get a circular reference), and you have additional quotes in there as well. Try

    FinalDecDate: Format([Final_Dec_Date_New_or_Reprint],"dd/mm/yyyy")
    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 ↓↓

  7. #7
    Welshgasman is online now Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    656
    Format also creates a string?, so Excel might not recognise that as a date?

    I just exported data with a date from a query to Excel and the date was recognised as per the query?
    Please, please use # when posting code snippets.
    Cross Posting Etiquette: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  8. #8
    matey56 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    40
    Quote Originally Posted by Welshgasman View Post
    Format also creates a string?, so Excel might not recognise that as a date?

    I just exported data with a date from a query to Excel and the date was recognised as per the query?
    My report exports MOST of the controls as dates except the few that are calculated controls. The dates are correct, but Excel views them as "general" and not dates.

  9. #9
    matey56 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    40
    Quote Originally Posted by Minty View Post
    You can't call the result the same as another field or the field in the calculation (you will get a circular reference), and you have additional quotes in there as well. Try

    FinalDecDate: Format([Final_Dec_Date_New_or_Reprint],"dd/mm/yyyy")
    This expression doesn't work. Even after adding the required equal sign.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,309
    Loose the FinalDecDate: part, in the control source you should only have = Format([Final_Dec_Date_New_or_Reprint],"dd/mm/yyyy") where [Final_Dec_Date_New_or_Reprint] is a the name of the calculated field in the report's record source.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,309
    Or try wrapping the calculated fields in CDate() (in the query that feeds the report and make sure you handle any nulls).
    Cheers

  12. #12
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,715
    Apologies I mis-read this as an exported query, not a report.

    I have had very limited success exporting reports to Excel in a useable fashion, so always use a query and format the Excel with automation.
    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 ↓↓

  13. #13
    matey56 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    40
    Quote Originally Posted by Gicu View Post
    Loose the FinalDecDate: part, in the control source you should only have = Format([Final_Dec_Date_New_or_Reprint],"dd/mm/yyyy") where [Final_Dec_Date_New_or_Reprint] is a the name of the calculated field in the report's record source.

    Cheers,
    The weird thing is, when I add this to the control source it works perfectly in the report view. However, when I export it to Excel the dates are correct in the first two rows then all the rest are 1/3/2022 in the entire column. It's so weird.

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,309
    Could you post a small sample with just the report and any dependent objects (no sensitive data please, just some "dummy" records to illustrate the issue)?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  15. #15
    matey56 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    40
    Quote Originally Posted by Gicu View Post
    Could you post a small sample with just the report and any dependent objects (no sensitive data please, just some "dummy" records to illustrate the issue)?

    Cheers,
    This is a very large DB with lots of sensitive data. Not sure I know an easy way to do that?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Exporting to Excel
    By cebrower in forum Queries
    Replies: 5
    Last Post: 09-02-2020, 09:48 AM
  2. Problem exporting dates to Excel
    By Freebird in forum Import/Export Data
    Replies: 2
    Last Post: 07-19-2016, 06:57 AM
  3. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  4. Issue recognizing Excel 'ranges'
    By Captain Database ...!! in forum Import/Export Data
    Replies: 4
    Last Post: 07-18-2011, 02:22 PM
  5. Exporting to Excel Help
    By Coffee in forum Import/Export Data
    Replies: 4
    Last Post: 07-18-2011, 07:37 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 - Senior Forums