Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Access to Excel is rounding numbers (XP)

    A field in my database table has 4 decimal places. (Settings on this field are Field Size: Double, Format: Standard, Decimal Places:4.) When I copy the data from that table and paste it into Excel, only 2 decimal places show up for that field. Either Excel or Access is rounding off the number and chopping off two of the decimal points. Is there something special I need to do to make Excel receive it as 4 decimal places? or is Access the problem?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access to Excel is rounding numbers (XP)

    The Format property only determines how the numbers are displayed, it has nothing to do with the way the numbers are stored in the table. So if you see 1,2345 in your table or form, the number stored may be 1,23448888 or 1,234530102 etc.
    When you import the data into Excel, the stored numbers will be imported. How many decimal places Excel displays depends on two factors: the column width and the number format. Play around with these; you will probably find that all decimals are still available.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Access to Excel is rounding numbers (XP)

    The number displayed in Access is 5.6869. If I copy the row of data and paste into Excel, the number in that field becomes 5.69. Even if I tell Excel to format with 4 decimals, the result is 5.6900 rather than 5.6869. When I try copying only the single cell from Access and pasting it to Excel, I get 5.6869, but I have 12,000 records and can't possibly do that for each and every cell. There must be a reason this is happening and a way to correct the problem. I really need the 4 decimal places in Excel.

  4. #4
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Access to Excel is rounding numbers (XP)

    I figured it out, sort of. If I export the table to an Excel file rather than using copy and paste, I get all four decimals. Problem solved. Thanks for your input Hans.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access to Excel is rounding numbers (XP)

    I can't reproduce this behavior, so I don't know what causes it. The Knowledge Base mentions that numbers are truncated to two decimal places when you export to a text file, but nothing about exporting numeric fields to Excel.

    Some things to try:

    1. Export the table to Excel instead of copy and paste. If you don't want to export the entire table, create a query that selects the required fields and records and export the query.

    2. Create a query that returns the number field as a text formatted to 4 decimal places: Format([FieldName], "0.0000")
    Try and see what happens if you copy and paste rows from this query, or if you export this query to Access.
    If the behavior is the same as on my system, the field will be transferred to Excel as text (with four decimal places). Assuming that you use Excel 2002, a smart tag will alert you to the fact that numbers have been stored as text, with an option to convert them to numbers.

    3. You haven't accidentally set "Precision as displayed" in Excel, have you (Tools | Options..., Calculation tab)?

Posting Permissions

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