Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2006
    Posts
    5

    Unanswered: rounding problem in access (very urgent)

    hi all
    i have a problem in rounding in access
    0.381*2.5% =0.009525
    however Access only give me ans = 0.0095

    can any one help me ? why is that so and how to overcome it.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your question is not complete.
    what are you doing, where are you seeing this rounded answer, what datatypes are you using for each of these reals?

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what display format are you using to display this information?
    you may need to explicitly set a display rule if you require more than 4 decimla places.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2006
    Posts
    5
    hi thank you very much for your prompt reply
    i am actually doing update query using access.
    i need to derive the correct pricing

    eg.i need to get the discounted price in this case
    S$ 0.381 * (S$ 0.381*2.5%) = 0.371

    however the query result give me 0.372, which would result in
    wrong pricing

    after a few attempt, i noticed that the rounding seems like truncated.
    0.381*2.5% = 0.009525
    however if the access computed, the result is 0.0095 which is result in a rounding up to 0.372.

    i think i need to use update query instead of VB or ADO as i have already
    created a macro which make up for a few update queries.

    or is there any where that i can do ?
    can i go to the table property to change something like "interger",
    currency" ??? i have tried to change the property of query but
    not successful

  5. #5
    Join Date
    Feb 2006
    Posts
    5
    my enviroment is MS Access 2002

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so where are you seeing the four decimal places?
    is it in a report
    is it in a form
    what data types are you using for the affected column(s)
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Feb 2006
    Posts
    5
    it is when i run the update query result.

    thnak for your reply.

    i think i may be find the solution already.
    i went to table design, and intially the field is "currency" , i changed it to
    "number" , field size to "double" , and "standard" . then i run the update
    query to write the value into the cell, i think it works.

    what do you think?

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    choices choices choices!

    if you stay with currency datatype, you only have four decimals to play with but the answer is precise.

    if you go with double, you enter the vague world of floating point errors.

    what sort of pricing are you playing with anyway where the fifth decimal has some sort of significance??

    you also have the option of the decimal datatype - but it is a complete pain to work with.

    my view: stay with currency datatype

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Feb 2006
    Posts
    5
    Oh i see thank you for highlighting it to me...
    then i shall consider..
    actually i am doing unit trust pricing.
    i am a litter worry after you have pointing out to me.
    for the example that i mentioned earlier,
    but when you used the calculator to compute, it would
    be S$ 0.371 then the Unit Trust "Unit" allocated to customer
    would be differ if the price is S$ 0.372......

    eg. customer investment S$ 1,000.00
    units allocated would be 2,688.17 uts( price = S$ 0.372)
    units allocated would be 2695.42 uts (price = S$ 0.372)
    quite a big difference

    i think maybe i leave with no choice but have to use "double"

    maybe maybe.....

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    recommendation
    if you are playing with people's money, used scaled-integers (currency or decimal or home-made). do not (not) use double for data storage.

    you can make your own scaled integer from a longinteger (if you have enough digit space!!! - a long has around 9 digits of space... if you play with longs you can express a price to any degree of decimals you like but only with +/- 9 "significant" digits ...and you have to do the scaling yourself)

    check out the decimal datatype in help... i think it it is designed for exactly what you are doing but it is (uinfortunately) incompletely implemented in Access which makes many operations more difficult than they should be.

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Feb 2006
    Posts
    56
    yes use cdec()


    ex. paste this in a command button on your form


    Dim test1
    test1 = 0.381 * 0.025
    test1 = CDec(test1)
    MsgBox test1

    It gives you I believe what your looking for.

    Thanks,

    John

Posting Permissions

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