Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    zero balance not detected (Win ME/Office 97)

    I add up several fields and, on a zero balance, through an Update query, turn an On/Off field off. It works most of the time, but occasionally a zero balance field is not detected and the On/Off field is not correctly updated. The field is numeric, double, with fixed format, 2 decimals. The fields have been rounded and truncated. I use the NZ function to not disrupt the calculations when fields are blank. The round and truncate operation uses a KB function called TruncCC(), as follows:

    X = X + RoundingFactor
    TruncCC = Int(X * FactorTemp) / FactorTemp
    Rounding factor is .005 and FactorTemp is 100.

    I apply this function in VB after calculations involving div or mult.

    As a test, I have manually re-entered the data with two decimals in the fields to be added, and still the query did not work. I am confused.

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

    Re: zero balance not detected (Win ME/Office 97)

    Your problem is that you are working with doubles. That it is displayed in fixed format with 2 decimal places is irrelevant. as is the rounding. Even if rounded to 2 decimals, what you see as 1.23 might actually be 1.230000000001! The only way I know to handle this situation is to convert to currency, then do your rounding, then compare currency fields.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: zero balance not detected (Win ME/Office 97)

    Any floating point calculation is subject to these kind of problems. There are some numbers that simply cannot be accurately expressed as floating point numbers, regardless of the formatting you apply - one example is the fraction 2/3 which gives you 0.6666666666......... so if you start adding and/or subtracting such numbers trying to get a zero, you may get a small number but not zero. There are two basic solutions to the problem - round the resulting sum to see if it rounds to zero, or switch to using a BCD (Binary Coded Decimal) type of number such as is used with currency fields.
    Wendell

  4. #4
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: zero balance not detected (Win ME/Office 97)

    Won't the TruncCC function eliminate any extraneous digits after two decimals? The function uses the Int function, which I thought would take care of that. The currency type still uses four decimals. Wouldn't I continue with the same problem? I don't use the currency type because of the repeating $ signs, which I find unattractive in a long column of figures. Is there any way to get rid of that? I could always make the compare versus a range of figures rather than versus zero. How about something like If Diff > .00001, than I set my On/Off field to Off. Not very eligant, but should fix the problem that we are talking about.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: zero balance not detected (Win ME/Office 97)

    Since the whole expression in your function is NOT wrapped in an Int() function, you're still dealing with a double and precision is unpredictable.
    Charlotte

  6. #6
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: zero balance not detected (Win ME/Office 97)

    After looking at the replies, obviously the solution is to use the Currency data type, applying the same TruncCC function; however, I still would like to get rid of the repeating $ sign associated with the Currency data type. Can that be done?

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: zero balance not detected (Win ME/Office 97)

    If you are using Currency data type, you can specify Standard number format with two decimal places to display data without the dollar signs. The amount $1,234.56 will be displayed as 1,234.56.

    For more information on floating point rounding errors, see MSKB Article 210423:

    ACC2000: Rounding Errors When You Use Floating-Point Numbers

    HTH

  8. #8
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: zero balance not detected (Win ME/Office 97)

    Here is a footnote from the KB article on the rounding function TruncCC. Guess if I had read a bit more carefully, I could have avoided all of the discussion:

    The user-defined functions should only be used with Currency data. If used with Double or Single numbers, you may still receive minor rounding errors. The reason for this is that Single and Double numbers are floating point. They cannot store an exact binary representation of decimal fractions. Therefore, there is always some error. However, Currency values are scaled integers and can store an exact binary representation of fractions to four decimal places.

    Thanks to you all for the help.

  9. #9
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: zero balance not detected (Win ME/Office 97)

    What is the behavior of a field when it is converted from double to currency? Is the field just truncated to 4 decimals? For example, I have an existing table, and go in and change the data type from double to currency. What happens to the existing data?

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

    Re: zero balance not detected (Win ME/Office 97)

    You wrote:
    >>What is the behavior of a field when it is converted from double to currency? Is the field just truncated to 4 decimals?<<

    I believe you will find Access will round the information to 4 decimal places, not truncate.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: zero balance not detected (Win ME/Office 97)

    Currency is a shorter datatype than Double so it should warn you and if you choose to proceed, truncate the decimal precision of the number. That's assuming you're changing the datatype and not just the display format of the field.
    Charlotte

Posting Permissions

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