1 <> 1???

[darren via AccessMonster.com]

I have an unbound text box in the footer of a continuous form that use the
sum function to add up %s(Doubles formatted as percentages). The trouble is
sometimes it is correct and other times it is not. I have tried increasing
the decimal places to see whether it is a matter of rounding but this is not
the case.

I also have code that uses this text box and when I test the value of it
using debug print it will = 1 and then pass through in an if statement which
checks to see whether it <>1 ???? (i.e. it says it equals 1 and then
disagrees with itself).

This is also evident in the conditional formatting that highlights it if the
value/sum <> 1 (i.e. 100%) It will displays 100% highlighted as it reckons it
does not equal 1.

??? Am I missing something ???

 

[Douglas J Steele]

Just as humans can't represent certain numbers exactly in base 10 (think of
one third or two thirds), so too are there numbers that computers can't
represent exactly in binary.

The number might be formatted to appear as 1, but in actual fact, it might
be 0.9999993653 or 1.000000345.

The usual approach is to check whether the difference between the two
numbers is "close enough" (where only you can determine what "close enough"
means).

Rather than If x = y Then, use If Abs(x-y) < 0.005 Then (or whatever you
decide is close enough)

 

[BruceM]

Maybe somebody here will recognize from your general description a familiar
problem, but most likely you will need to post the code and other details
before anybody can offer a targeted suggestion.

 

[darren via AccessMonster.com]

The code is very basic:

"If Me.fsubPolPortFunds.Form.txtTotalPortFundpc.Value <> 1 Then ........"

If I use:
Debug.Print Me.fsubPolPortFunds.Form.txtTotalPortFundpc.Value

then I get 1 returned, but it still passes through this arguement thinking
that it does not equal 1.

As for the numbers entered they are not calculated at all and are single
decimal space manual entries e.g. 0.7, 0.1, 0.1 ,0.1 formatted as %.

The text box displays 100% but seems to think that its value does not equal 1
and the code arguement seems to agree with it?

 

[Douglas J Steele]

Did you try my suggestion?

If Abs(Me.fsubPolPortFunds.Form.txtTotalPortFundpc.Value - 1) < 0.0005 Then

 

[BruceM]

The usual format for referencing a subform control is:
Forms!frmMainForm!fsubPolPortFunds.Form!txtTotalPortFundpc
I don't know if that will make any difference.
I could not re-create the problem. I used a number field (double) in the
subform record source, and formatted the subform control (text box) as
percent. I also formatted the unbound Sum text box as percent.
You seem to have left something out of your explanation. How are you
running the code? Is it a command button, Before Update, Exit, or what?
What comes after Then ...? What is txtTotal Port Fundpc?

 

[Terry Kreft]

It's floating point arithmetic which byit's nature canot be exact.

Doug has already explained one strategy to get around your problem.

The other way is to use either the currency or decimal datatypes.

 

[BruceM]

There are some problems with Decimal. See
http://allenbrowne.com/bug-08.html.

 

[darren via AccessMonster.com]

Hi Doug, yes I tries this but unfortunately it did not work.

In the detail of the continuous form PFunpc is a bound textbox formatted as
Percent with Auto decimal places as is the unbound textbox. As an example I
am entering 0.7, 0.1, 0.1, 0.1. The unbound text box, controlsource=Sum(
[PFunpc]), shows 100%. The unbound text box has conditional formatting where
if the field value is not equal to 1 then to go bold. However it displays 100%
in bold.

If I test the control this in the intermediate window it returns a value of 1
..

As such the code and the conditional formatting seem to agree that it does
not equal 1 dispite displaying 100%. If I set the unbound box's format to
general number with 15 decimal places then it still displays 1 .

?

 

[Marshall Barton]

The code is very basic:

"If Me.fsubPolPortFunds.Form.txtTotalPortFundpc.Value <> 1 Then ........"

If I use:
Debug.Print Me.fsubPolPortFunds.Form.txtTotalPortFundpc.Value

then I get 1 returned, but it still passes through this arguement thinking
that it does not equal 1.

As for the numbers entered they are not calculated at all and are single
decimal space manual entries e.g. 0.7, 0.1, 0.1 ,0.1 formatted as %.

The text box displays 100% but seems to think that its value does not equal 1
and the code arguement seems to agree with it?

As Doug points out, it is common for floating point number
precision to be less that the entire value. But, since you
have so few digits in your actual data, I think you would be
better off using a fixed point approach. The Currency data
type has four decimal places with no loss of precision so if
you change your table field from Double to Currency, your
problem should disappeared.

 

[darren via AccessMonster.com]

Hi Bruce, changing the manner in which it is referenced has not changed the
result. The code is in the Forms Unload event and it is simply to prompt the
user on closing to accept that the allocation does not add up to 100% (if it
doesn't).

It seems so stupid I am sure I must be at fault somewhere but I just can't
see it.

It sounds as though I need to get my head around Terrys comment on floating
point arithmetic. I understand the example of a third, but I don't see how
this would apply to the samples I've given. I'll try it investigating it
anyway.

 

[darren via AccessMonster.com]

Hi Terry, so should a field designed to contain percentages be sized as
decimal and not double?

 

[Albert D.Kallal]

You should use currency type....

take a look at:

Public Sub TestAdd()


Dim MyNumber As Single
Dim i As Integer

For i = 1 To 10
MyNumber = MyNumber + 1.01
Debug.Print MyNumber
Next i
End Sub


Here is the actual outpput of the above:


1.01
2.02
3.03
4.04
5.05
6.06
7.070001
8.080001
9.090001
10.1


You can see that after just 7 addtions..already rounding is occuring


and if we add the follwing line of code to the end of the above:


if MyNumber = 10.1 = True then

msgbox "the number is 10.1"
else

msgbox "the number is somthing else"

endif


The above will actuall produce:


the number is something else

So, just have 10 simple additions, we can't even check the number = 10.1,
and yet that is what it prints as....

As mentioned by others, you can't represnet fractiaonal numbers in a
computer, and then "test" the value. You have to take into account rouding,
or use interger values. For *all* business applations that involves numbers
(accouting stuff), you thus MUST use currency data types, or expaince
roudning errors....

 

[Terry Kreft]

Whoops, I'd forgotten that one Bruce, thanks for the catch.

 

[Terry Kreft]

It depends what you are doing.

With the problem you are experiencing I would look at using the currency
data type.

 

[darren via AccessMonster.com]

First of all thanks to everyone so far. The cogs are slowly turning and I'm
beginning to understand. Please also bear with me as I grasp the details of
this new found gap in my knowledge/understanding. (I'm self taught if you
hadn't guessed)

I still don't quite grasp why rounding occurs in this instance but perhaps it
is sufficient, for now, for me to know that it does. It had never really
occured to me that Currency would be for anything other than Currency.

So, with regard to this issue, in designing the tables we are discussing data
type and field size. Am I correct in summaring that the key difference
between Number and Currency is that Currency is fixed at a maximum of four
decimal places and does not round, whereas, Number rounds and has greater
flexibility with regard to decimals?

What guidelines/best practice can I draw from this as to when to us either e.
g. Albert says "For *all* business applications that involve numbers
(accounting stuff), you thus MUST use currency data types, or experriance
roudning errors...."

Finally, setting the type to Currency does indeed remedy the situation. If I
do this on the live system what harm am I likely to do to any existing data?
At present all fields for percentages are currently set as Double. Most data
is manually input and is therefore unlikely that any have more than four
decimal places so I hope changing the type to Currency will have no advserse
effects. However, some data is imported and is the result of complex Excel
calculations.

Thanks once again.

 

[Marshall Barton]

First of all thanks to everyone so far. The cogs are slowly turning and I'm
beginning to understand. Please also bear with me as I grasp the details of
this new found gap in my knowledge/understanding. (I'm self taught if you
hadn't guessed)

I still don't quite grasp why rounding occurs in this instance but perhaps it
is sufficient, for now, for me to know that it does. It had never really
occured to me that Currency would be for anything other than Currency.

So, with regard to this issue, in designing the tables we are discussing data
type and field size. Am I correct in summaring that the key difference
between Number and Currency is that Currency is fixed at a maximum of four
decimal places and does not round, whereas, Number rounds and has greater
flexibility with regard to decimals?

What guidelines/best practice can I draw from this as to when to us either e.
g. Albert says "For *all* business applications that involve numbers
(accounting stuff), you thus MUST use currency data types, or experriance
roudning errors...."

Finally, setting the type to Currency does indeed remedy the situation. If I
do this on the live system what harm am I likely to do to any existing data?
At present all fields for percentages are currently set as Double. Most data
is manually input and is therefore unlikely that any have more than four
decimal places so I hope changing the type to Currency will have no advserse
effects. However, some data is imported and is the result of complex Excel
calculations.

Rounding occurs with some fractions no matter what system
you are using to do arithmetic. With a pencil and paper
decimal system the number 1/3 is written as .3333333...
Eventualy you get tired of writing 3s and just say that's
good enough. Similarly 2/3 is .666666... so their sum is
..9999999... and you mentally "round' that to 1. Almost all
computer systems use binary (instead of decimal) so
different numbers end up with a repeating sequence that can
not be precisely represented exactly regardless of how many
places are used (Single/Double). E.g. the decimal number
1.2 is the binary number 1.001100110011.... so you have the
same problem that others have explained how you can check
for whatever you decide is "good enough".

OTOH. fixed point arithmetic (e.g. Currency) on a decimal
number with a fractional part such as 1.2 converts and
operates on it as the integer 12000. If you want to put 1/3
as a fixed point number you must first decide how you are
going to input the number. Most likely you will mentally
"round" it to .3333 You would almost certainly enter 2/3
as .6667, probably without even thinking about how both
numbers are not exactly correct. Also note that while
summing fixed point numbers will not intoduce any additional
"rounding errors", multiplying and dividing can.

From the above, you should be alble to determine that
converting you table fields from Double to Currency will not
lose any information as long as your existing numbers have
four or fewer decimal places.

 

[Albert D.Kallal]

So, with regard to this issue, in designing the tables we are discussing

data
type and field size. Am I correct in summaring that the key difference
between Number and Currency is that Currency is fixed at a maximum of four
decimal places and does not round, whereas, Number rounds and has greater
flexibility with regard to decimals?

Correct. To be even more specific, currency is actually a scaled integer.
That simply means that it is a integer value for internal calculations. It
never represents a fraction like 3/10 (which of course is repsentd by ".3",
which when repsresnted in a floating point number is not exact).

So, when doing fincicnal calucations, you do want to use currency. (or, at
least *after* you are done, store the reuslts in a currecntly type, as the
"extra" fractinal vlaues will be discarded.

In some cases, and other programming lanauges, when you don't have bult in
currency type, you use "large" intergers.

And, even in ms-access, if you need 6 digits of accurery, then you can
simply SCALE your numbers by 100.

Eg:

150.25 becomes 15025
4.7 becomes 470

Then, do your calucations, and then when done, smiply de-scale the results.
However, I don't think you need this concpet at all in your case...

unlikely that any have more than four
decimal places

Well, if you have more then 4 places, you will be loosing some data if you
switch that field to currency. Only you can decide this issue.

However, you should have no problems for the most part if you change field
to currency type. (and, assuming no more then 4 decimal places have been
used).

However, some data is imported and is the result of complex Excel
calculations.

Excel actually uses large integers to prevent those rounding errors also.
However, code written Excel suffers the *exact* same problem as ms-access.
(they both use VBA). So, using code, or macros written in Excel are not
immune to this problem either.....

 

[darren via AccessMonster.com]

Thanks all once again. I've learned alot.