Results 1 to 9 of 9
  1. #1
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49

    Export to Excel results in new decimal values

    I am trying to export tables from Access 2013 into Excel 2013. Decimals other than .00, .25, .5, and .75 export incorrectly into Excel.



    For example

    Access value Excel Value
    231.01 231.009994506836
    232.6 232.600006103516
    232.70
    232.699996948242
    233.0 233.0
    235.8 235.800003051758


    Any idea what is going on here? If I copy the values directly from the Access table and paste into an excel table the extra decimal values are not added, it only occurs when I use the export option in Access. I cannot copy and paste because I often need to export >100,000 records at once.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I would store the access values in Currency field. They only has 4 decimals.
    it may reduce the long decimal when transferred.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    are you sure your access values aren't formatted to only show 1 or 2 decimals? Exporting does not export the format, only the underlying value

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    format is an illusion for the user. The number is still 20 decimals, but you only see 2 in format(num,2)
    if you dont want to alter the field, convert it in a query, try CCUR(field)

    to make it currency .

  5. #5
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49
    Even if the value is 20 decimals, why would my original value of say 235.8 get changed to 235.800003051758? The original value I input was 235.8 so I do not know where those extra numbers are coming from.

  6. #6
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49
    Even though I don't want values displayed as currency, I tried that conversion, and the values in excel still carry extra non-zero decimals.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    decimals can be fickle - see these links for an explanation.

    http://www.cpearson.com/excel/rounding.htm
    http://stackoverflow.com/questions/2...-places-does-a

    in the second link, pay notice the answer and a further link

    As ranman has suggested use a currency datatype

  8. #8
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49
    The problem seems to be in the database itself. I created a new db with the same values, exported into excel, and the values no longer have the additional digits. I'll triple check all formatting and let you know if/what I find.

  9. #9
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49
    Still not figuring it out? I also tried, as a test, a simple update query to replace all instances of 235.800003051758 with 235. The query resulted in 0 records being updated (without any violations). Still confused...

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

Similar Threads

  1. Export Query Results to Excel Template
    By laterdater in forum Macros
    Replies: 2
    Last Post: 09-25-2015, 11:20 AM
  2. export total number of results to excel
    By xopherira in forum Import/Export Data
    Replies: 3
    Last Post: 08-25-2015, 10:21 AM
  3. Replies: 1
    Last Post: 04-30-2012, 05:10 PM
  4. Export Filtered Results to Excel Tabs
    By opod in forum Programming
    Replies: 1
    Last Post: 08-03-2011, 01:33 PM
  5. Get the sum of decimal values - weird results
    By BengtCarlsson in forum Queries
    Replies: 2
    Last Post: 02-10-2006, 04:29 PM

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