Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Formatting percentage

    I have an Access 2010 report, which is based on a query, which includes a "Net surcharge or discount" field.

    The data is stored in a table as a number in a field named "Adjustment".

    The query uses a calculated field to convert the data in the table to a decimal in the query (Net S/D: [Adjustment]/100), which correctly converts (e.g.) 125 to 1.25, or -50 to -.5.

    The report uses a format mask to display the field from the query as positive, negative, or blank (+#%;-#%;"";""), which correctly displays (e.g.) 1.25 as +125%, or -50 as -50%.

    However, when I export the report to an Excel file, every cell in the column for this field is highlighted with an error ("Number stored as text"), with a "Convert to number" option, which works but needs to be done manually on each cell. How can I adjust the formatting in the report so that the field is exported to Excel with the correct format applied automatically?

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Midwest, USA
    Posts
    108
    Thanks
    5
    Thanked 5 Times in 4 Posts
    How does it display if you tell Excel it is text?

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    This seems to have fallen into the vanished posts crevice till now. The issue is with exporting the report to Excel - in reports everything is text. If you want to get a decent looking Excel workbook, export the data source for your report to Excel and then tweak the format on the "Net surcharge or discount" to show it in percentage. In general exporting Access reports to Excel doesn't work very well, but the export of query data is pretty smooth. The only issue then is you have to tweak the format to make it more readable. If you do that sort of thing frequently for a specific task, you might want to look at using Automation of Excel from Access to create the workbook. We've done that very successfully in the past.
    Wendell

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your replies. This appears to have been updated in Access and/or Excel recently, as this now behaves differently, but still not properly. Now, the format mask from the Access report (+#%;-#%;"";"") is automatically exported and applied in Excel as Custom formatting on the field, which now is not highlighted with a "Number stored as text" error. However, a cell appears blank in Excel even if contains a value exported from Access, but if the cell is then manually overtyped with the same value, it appears correctly (see attached example). What could be causing this?
    Attached Files Attached Files
    Last edited by Murgatroyd; 2013-09-26 at 03:27.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •