Results 1 to 11 of 11

20021110, 12:50 #1
 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 reentered the data with two decimals in the fields to be added, and still the query did not work. I am confused.

20021110, 13:06 #2
 Join Date
 Jun 2001
 Location
 Crystal Beach, FL, Florida, USA
 Posts
 3,465
 Thanks
 1
 Thanked 40 Times in 39 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.

20021110, 13:09 #3
 Join Date
 Aug 2001
 Location
 Evergreen, CO, USA
 Posts
 6,648
 Thanks
 3
 Thanked 65 Times in 64 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

20021110, 15:27 #4
 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.

20021110, 15:39 #5
 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

20021110, 16:24 #6
 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?

20021110, 18:42 #7
 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 FloatingPoint Numbers
HTH

20021112, 12:54 #8
 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 userdefined 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.

20021115, 17:43 #9
 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?

20021115, 20:05 #10
 Join Date
 Jun 2001
 Location
 Crystal Beach, FL, Florida, USA
 Posts
 3,465
 Thanks
 1
 Thanked 40 Times in 39 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.

20021116, 02:33 #11
 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