Results 1 to 4 of 4
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rounding: value not accepted, sum shows wrong (97)

    All fields in the tblVoorschot2005 table except Gemeente are defined as Single precision floating point numbers. Single precision numbers only have 7 significant digits, so it's to be expected that 390611.19 cannot be stored exactly - it has 8 significant digits. You should either use Double precision floating point numbers, or the dedicated Currency (Valuta in Dutch/Flemish) data type. Double precision has 15 significant digits, and Currency has 15 significant digits in the whole number part, and 4 in the decimal part.

  2. #2
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Rounding: value not accepted, sum shows wrong (97)

    Copy-paste rounded values from Excel

    Most valuable members of this board,

    I've been browsing through a few (interesting) threads about rounding... but didn't found a solution (some indications although... but too theoretical... or is it just me getting tired...):
    - Access can't store the value 390611.19 (1)
    - I can't get a sum right: ....19 + ....30 + ....00 = ....49 (2)

    More details: I've once copied from Excel original values (without rounding) for three attributes (A,B,C) as well as their corresponding rounded values (A_Round,B_Round,C_Round). Now I found out that in the...
    (1) table, record town 1: if 390611.19 is entered in field A_Round, it is automatically transformed in 390611,875;
    (2) report, I can't get the sum right, whether I use just formatting, Dev Ashish' Round function (w/o Bankers Rounding) or the Int(..*100)/100 trick. The report shows the result of different approaches I've tried.
    Rounding is needed to two decimals with .xx5 rounded upwards.

    I'm afraid I can't get this right 'cause I'm not completely understanding what I'm doing... So any suggestion is appreciated :-).
    What can I do?
    The only thing I can imagine now (but still have to find out if it works) is to multiply the Excel by 100 and rounding them to long integer before copying them to Access (using Long Integer fields) and then, in the report, show the values/100... which seems quiet ugly :-p.

    Hasse

    p.s. In the future, the numbers used will be calculated as well by the database (where they're now imported from excel - we're in transition). Then, I guess, as I experienced with the use of Ashish' function in another database, this problems shouldn't occur as these values which Access can't store, are only used as intermediate steps in a calculation and not stored as field values.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Rounding: value not accepted, sum shows wrong (97)

    Given how you want to use the numbers, you should use a Currency data type in Access.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Rounding: value not accepted, sum shows wrong

    Hans & Mark,
    these are times that make me wonder whether there aren't too many holes in my knowledge and it seems time to refresh the basics <img src=/S/RollEyes.gif border=0 alt=RollEyes width=19 height=19>.
    Thanks really for your answer & info - I owe you a beer for the time you've saved me <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>.
    Many greetings,
    Hasse

Posting Permissions

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