Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Problem in datatype in calculation

Discussion in 'Information Technology' started by Keith, Oct 31, 2005.

  1. Keith

    Keith
    Expand Collapse
    Guest

    I have a UnitPrice field and a Quantity field both of Single Type.
    I'm totally confused and frustrated with the different results I get when I
    multiply these two fields to get a TotalValue.
    When I have a Quantity of 4,032,000 multiply by a Unit Price of 0.03728,
    instead of getting an exact amount of 150,312.96, I get 150,312.96875.
    When I tried CDbl(UnitPrice) * CDbl(Quantity), I get 150,312.963009. This is
    still acceptable as I'll only keep 2 decimal points in the TotalValue. But
    when I use this conversion, I had problem with another set of figures. This
    time when I use a Quantity of 165,200 multiply by a UnitPrice of 1.065, I get
    175,938.009453 which is wrong if I round it off to 2 decimal places.
    I really hope there's help asap and really appreciate the help

    Thanks & Regards,
    Keith
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    How can I make decent and useful friends and from where ?? most people have a problem with me Sikh Youth Oct 23, 2013
    The Problem with Taking Too Many Vitamins Health & Nutrition Oct 18, 2013

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Sun, 30 Oct 2005 19:35:06 -0800, "Keith"
    <Keith@discussions.microsoft.com> wrote:

    >I have a UnitPrice field and a Quantity field both of Single Type.
    >I'm totally confused and frustrated with the different results I get when I
    >multiply these two fields to get a TotalValue.
    >When I have a Quantity of 4,032,000 multiply by a Unit Price of 0.03728,
    >instead of getting an exact amount of 150,312.96, I get 150,312.96875.
    >When I tried CDbl(UnitPrice) * CDbl(Quantity), I get 150,312.963009. This is
    >still acceptable as I'll only keep 2 decimal points in the TotalValue. But
    >when I use this conversion, I had problem with another set of figures. This
    >time when I use a Quantity of 165,200 multiply by a UnitPrice of 1.065, I get
    >175,938.009453 which is wrong if I round it off to 2 decimal places.
    >I really hope there's help asap and really appreciate the help
    >
    >Thanks & Regards,
    >Keith


    Single and Double are both "Floating Point" numbers. They're stored as
    a binary fraction times an exponent. Single is accurate to about seven
    decimal places (so your first result makes sense); Double is accurate
    to about 14 places. Neither is exact.

    The solution is to not use ANY "Number" datatype. Instead, use a
    Currency datatype field, and (if need be) CCur() to convert the value
    to Currency. A Currency datatype is accurate to exactly four decimals,
    with no roundoff error, and a range into the trillions.

    John W. Vinson[MVP]
     
  4. Ofer

    Ofer
    Expand Collapse
    Guest

    try
    round([Quantity] * [Unit Price] ,2)

    The 2 specify two decimal places
    --
    If I answered your question, please mark it as an answer. That way, it will
    stay saved for a longer time, so other can benifit from it.

    Good luck



    "Keith" wrote:

    > I have a UnitPrice field and a Quantity field both of Single Type.
    > I'm totally confused and frustrated with the different results I get when I
    > multiply these two fields to get a TotalValue.
    > When I have a Quantity of 4,032,000 multiply by a Unit Price of 0.03728,
    > instead of getting an exact amount of 150,312.96, I get 150,312.96875.
    > When I tried CDbl(UnitPrice) * CDbl(Quantity), I get 150,312.963009. This is
    > still acceptable as I'll only keep 2 decimal points in the TotalValue. But
    > when I use this conversion, I had problem with another set of figures. This
    > time when I use a Quantity of 165,200 multiply by a UnitPrice of 1.065, I get
    > 175,938.009453 which is wrong if I round it off to 2 decimal places.
    > I really hope there's help asap and really appreciate the help
    >
    > Thanks & Regards,
    > Keith
     
  5. Keith

    Keith
    Expand Collapse
    Guest

    Hi thanks for replying. It did not solve the problem entirely but was really
    close.
    I got this instead of 150,392.96
    4,032,000 * 0.03728 = 150,393.6
    It seems tat it rounds off to 3 decimal places for currency data type.

    Thanks,
    Keith

    "John Vinson" wrote:

    > On Sun, 30 Oct 2005 19:35:06 -0800, "Keith"
    > <Keith@discussions.microsoft.com> wrote:
    >
    > >I have a UnitPrice field and a Quantity field both of Single Type.
    > >I'm totally confused and frustrated with the different results I get when I
    > >multiply these two fields to get a TotalValue.
    > >When I have a Quantity of 4,032,000 multiply by a Unit Price of 0.03728,
    > >instead of getting an exact amount of 150,312.96, I get 150,312.96875.
    > >When I tried CDbl(UnitPrice) * CDbl(Quantity), I get 150,312.963009. This is
    > >still acceptable as I'll only keep 2 decimal points in the TotalValue. But
    > >when I use this conversion, I had problem with another set of figures. This
    > >time when I use a Quantity of 165,200 multiply by a UnitPrice of 1.065, I get
    > >175,938.009453 which is wrong if I round it off to 2 decimal places.
    > >I really hope there's help asap and really appreciate the help
    > >
    > >Thanks & Regards,
    > >Keith

    >
    > Single and Double are both "Floating Point" numbers. They're stored as
    > a binary fraction times an exponent. Single is accurate to about seven
    > decimal places (so your first result makes sense); Double is accurate
    > to about 14 places. Neither is exact.
    >
    > The solution is to not use ANY "Number" datatype. Instead, use a
    > Currency datatype field, and (if need be) CCur() to convert the value
    > to Currency. A Currency datatype is accurate to exactly four decimals,
    > with no roundoff error, and a range into the trillions.
    >
    > John W. Vinson[MVP]
    >
     
  6. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Sun, 30 Oct 2005 21:48:02 -0800, "Keith"
    <Keith@discussions.microsoft.com> wrote:

    >Hi thanks for replying. It did not solve the problem entirely but was really
    >close.
    >I got this instead of 150,392.96
    >4,032,000 * 0.03728 = 150,393.6
    >It seems tat it rounds off to 3 decimal places for currency data type.


    It's rounding off to four, and exactly four - just as I said in my
    message. 0.03728 is being rounded to 0.0373.

    Try 4,032,000 * 3.728 / 100.

    John W. Vinson[MVP]
     
  7. Keith

    Keith
    Expand Collapse
    Guest

    Hi John! I've managed to solve it finally using both of you suggestions.
    I used (Quantity * CCur(UnitPrice * 100)) / 100 and all calculations are
    perfect!
    Thanks alot for ur help!

    "John Vinson" wrote:

    > On Sun, 30 Oct 2005 21:48:02 -0800, "Keith"
    > <Keith@discussions.microsoft.com> wrote:
    >
    > >Hi thanks for replying. It did not solve the problem entirely but was really
    > >close.
    > >I got this instead of 150,392.96
    > >4,032,000 * 0.03728 = 150,393.6
    > >It seems tat it rounds off to 3 decimal places for currency data type.

    >
    > It's rounding off to four, and exactly four - just as I said in my
    > message. 0.03728 is being rounded to 0.0373.
    >
    > Try 4,032,000 * 3.728 / 100.
    >
    > John W. Vinson[MVP]
    >
     

Share This Page