Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Decimal places when copy to Excel (Acc 2002, XL 2002)

    A colleague has an Acc2002 FE linked to an A97 backend. He displays one of the forms in Datasheet view, then copies all the data onto the clipboard and pastes it into XL using PasteSpecial with CommaSeparatedValues. One of the fields is displayed with 4 decimal places of precision in Access, but only 2 decimals are copied into XL. I investigated the table design, and found that the field is defined with Fixed format and 4 decimals.

    As an experiment, I built a dummy database, table, and query, and tried the Copy/Paste function. It worked perfectly: it preserved all the decimal places. Next, I changed the field definition in his table to General Number, Auto decimals, and the Copy/Paste worked properly. HOwever, I am not certain why he had defined the Fixed format in the first place, and I am concerned that my changing the field structure might break something else in the database. There are several other fields in the table with the same problem that will also need fixing.

    Is there a better way to copy the data from Access to XL and still preserve all the proper decimals places without modifying the table structure?

    Thanks
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

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

    Re: Decimal places when copy to Excel (Acc 2002, XL 2002)

    The Format and Decimal Places properties of a field have no effect whatsoever on the way the data are stored in the database, they only affect the way the data are displayed. So if you change the Format property from Fixed to General Number, you won't modify the data in the table.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimal places when copy to Excel (Acc 2002, XL 2002)

    Thanks Hans As you say, modifying the format of the textbox in the form accomplishes what I need. I should have tried that first...
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimal places when copy to Excel (Acc 2002, XL 2002)

    Understood that the format property has no effect on the field contents, but try this to see how confusing some things can become:

    Create a new table (A 2002) with three fields, all defined as Double. Set their format properties to <blank>, Fixed2, and Fixed4 respectively. Create three records and type the same number into all three fields of each record:

    4.1234
    6.123456
    8.12345678

    The onscreen representation of the data in Access is exactly as expected. Now copy and paste all three records into Excel. The Fixed2 and "floating" field are displayed in Excel exactly as expected, but the Fixed4 field contains only two decimals of precision even though it is defined in Access with four decimals of precision.

    That's what I tried first in solving my colleague's problem, which lead me to believe that the field definition in the table was critical in order to get the desired Copy/Paste action. I have since learned that the textbox format in the form also has an effect.

    Thanks again for your help.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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