fractions summing to a string instead of a number

[Adam Bryce]

Hi,

I came across this when coding a form in Acc2002, and found I could
replicate it, but had no idea why it happens:

I create a form with four text boxes: A, B, C and Total
A - C have a default value of zero, and an afterUpdate event that calls a
function within the form code:

If [total] <> 1 Then
[total].forecolor = 255 'highlight text (red)
Else
[total].forecolor = 0 'normal text (black)
End If

The Total Box has a control source of:

=cdbl([A])+cdbl()+cdbl([C])

all the boxes are formatted as percentage*, with no d.p. - the idea being
that if they don't sum to 100%, the user is warned by the total turning red.
*I've tried it without formatting, and it doesn't change the problem at all.

My problem is that, with certain combinations of A, B and C, the box turns
red even if the values sum to 100.

For example having A-C being 30%, 35%, 35% respectively results in '100%'
displayed in red. However, A-C entered as 35%, 35% and 30% results in '100%'
displayed in black.

Having checked the code, it turns out that in the former case, the value of
[total] is being returned as "1" instead of 1.

I've worked around this in my real code by just changing the condition to <>
"1", but I'm still perplexed as to why the order of the numbers in the
expression changes the type of the result.

Can anyone help explain this, or even better, tell me where I'm going wrong
and how to avoid it in future?

Thanks,
Adam.

 

[Klatuu]

This has nothing to do with whether it is a string or not. It has to do with
rounding.
When your percentages are calculated, it is almost a certainty some of the
calculations will result in some decimal that is masked by your formatting.
So that the whole may appear to by 100%, but in fact it is something like
100.0000000003 (for example). That, then, will not evaluate to true. The
easy fix is to compare on the Int or the Fix of the total. That will drop
the decimals and evaluate to 100%

 

[Douglas J. Steele]

However, the sum could just as easily be 99.9999999993 instead of
100.00000000003, in which case Int or Fix won't work.

The usual approach from Numerical Methods is to look at the difference
between the number and the desired number, and see whether they're "close
enough".

Instead of

If [total] <> 1 Then

use

If Abs([total] - 1) > 0.0001 Then

(You may have to play with what value to use for "close enough")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This has nothing to do with whether it is a string or not. It has to do
with
rounding.
When your percentages are calculated, it is almost a certainty some of the
calculations will result in some decimal that is masked by your
formatting.
So that the whole may appear to by 100%, but in fact it is something like
100.0000000003 (for example). That, then, will not evaluate to true. The
easy fix is to compare on the Int or the Fix of the total. That will drop
the decimals and evaluate to 100%
--
Dave Hargis, Microsoft Access MVP

Hi,

I came across this when coding a form in Acc2002, and found I could
replicate it, but had no idea why it happens:

I create a form with four text boxes: A, B, C and Total
A - C have a default value of zero, and an afterUpdate event that calls a
function within the form code:

If [total] <> 1 Then
[total].forecolor = 255 'highlight text (red)
Else
[total].forecolor = 0 'normal text (black)
End If

The Total Box has a control source of:

=cdbl([A])+cdbl()+cdbl([C])

all the boxes are formatted as percentage*, with no d.p. - the idea being
that if they don't sum to 100%, the user is warned by the total turning
red.
*I've tried it without formatting, and it doesn't change the problem at
all.

My problem is that, with certain combinations of A, B and C, the box
turns
red even if the values sum to 100.

For example having A-C being 30%, 35%, 35% respectively results in '100%'
displayed in red. However, A-C entered as 35%, 35% and 30% results in
'100%'
displayed in black.

Having checked the code, it turns out that in the former case, the value
of
[total] is being returned as "1" instead of 1.

I've worked around this in my real code by just changing the condition to
<>
"1", but I'm still perplexed as to why the order of the numbers in the
expression changes the type of the result.

Can anyone help explain this, or even better, tell me where I'm going
wrong
and how to avoid it in future?

Thanks,
Adam.

 

[Adam Bryce]

The percentages aren't calculated - I'm typing in the values 0.35 etc, by
hand, and the form is just formatting them as %.

if I enter 0.3, 0.35, 0.35 and then check the value of int([total]*100), it
comes out as 99.

If I enter 0.35, 0.35, 0.3, the value of int([total]*100) comes out as 100.

I guess it is a rounding error - I didn't realise Access was quite that
inaccurate!

Thanks anyway,
Adam

This has nothing to do with whether it is a string or not. It has to do with
rounding.
When your percentages are calculated, it is almost a certainty some of the
calculations will result in some decimal that is masked by your formatting.
So that the whole may appear to by 100%, but in fact it is something like
100.0000000003 (for example). That, then, will not evaluate to true. The
easy fix is to compare on the Int or the Fix of the total. That will drop
the decimals and evaluate to 100%
--
Dave Hargis, Microsoft Access MVP

Hi,

I came across this when coding a form in Acc2002, and found I could
replicate it, but had no idea why it happens:

I create a form with four text boxes: A, B, C and Total
A - C have a default value of zero, and an afterUpdate event that calls a
function within the form code:

If [total] <> 1 Then
[total].forecolor = 255 'highlight text (red)
Else
[total].forecolor = 0 'normal text (black)
End If

The Total Box has a control source of:

=cdbl([A])+cdbl()+cdbl([C])

all the boxes are formatted as percentage*, with no d.p. - the idea being
that if they don't sum to 100%, the user is warned by the total turning red.
*I've tried it without formatting, and it doesn't change the problem at all.

My problem is that, with certain combinations of A, B and C, the box turns
red even if the values sum to 100.

For example having A-C being 30%, 35%, 35% respectively results in '100%'
displayed in red. However, A-C entered as 35%, 35% and 30% results in '100%'
displayed in black.

Having checked the code, it turns out that in the former case, the value of
[total] is being returned as "1" instead of 1.

I've worked around this in my real code by just changing the condition to <>
"1", but I'm still perplexed as to why the order of the numbers in the
expression changes the type of the result.

Can anyone help explain this, or even better, tell me where I'm going wrong
and how to avoid it in future?

Thanks,
Adam.

 

[Douglas J. Steele]

It's a fact of life with computers (nothing specific to Access).

The same problem exists in base 10. For instance, 1/3 is 0.33333 repeating,
so that 1/3 + 1/3 + 1/3 ends up as 0.99999. 0.3 and 0.35 are fractions
(3/10 and 35/100 respectively), and the same issue happens trying to
represent them in base 2 (or base 16 or however you want to think of it)

Check what Luke Chung had to say in
http://www.fmsinc.com/tpapers/math/index.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The percentages aren't calculated - I'm typing in the values 0.35 etc, by
hand, and the form is just formatting them as %.

if I enter 0.3, 0.35, 0.35 and then check the value of int([total]*100),
it
comes out as 99.

If I enter 0.35, 0.35, 0.3, the value of int([total]*100) comes out as
100.

I guess it is a rounding error - I didn't realise Access was quite that
inaccurate!

Thanks anyway,
Adam

This has nothing to do with whether it is a string or not. It has to do
with
rounding.
When your percentages are calculated, it is almost a certainty some of
the
calculations will result in some decimal that is masked by your
formatting.
So that the whole may appear to by 100%, but in fact it is something like
100.0000000003 (for example). That, then, will not evaluate to true.
The
easy fix is to compare on the Int or the Fix of the total. That will
drop
the decimals and evaluate to 100%
--
Dave Hargis, Microsoft Access MVP

Hi,

I came across this when coding a form in Acc2002, and found I could
replicate it, but had no idea why it happens:

I create a form with four text boxes: A, B, C and Total
A - C have a default value of zero, and an afterUpdate event that calls
a
function within the form code:

If [total] <> 1 Then
[total].forecolor = 255 'highlight text (red)
Else
[total].forecolor = 0 'normal text (black)
End If

The Total Box has a control source of:

=cdbl([A])+cdbl()+cdbl([C])

all the boxes are formatted as percentage*, with no d.p. - the idea
being
that if they don't sum to 100%, the user is warned by the total turning
red.
*I've tried it without formatting, and it doesn't change the problem at
all.

My problem is that, with certain combinations of A, B and C, the box
turns
red even if the values sum to 100.

For example having A-C being 30%, 35%, 35% respectively results in
'100%'
displayed in red. However, A-C entered as 35%, 35% and 30% results in
'100%'
displayed in black.

Having checked the code, it turns out that in the former case, the
value of
[total] is being returned as "1" instead of 1.

I've worked around this in my real code by just changing the condition
to <>
"1", but I'm still perplexed as to why the order of the numbers in the
expression changes the type of the result.

Can anyone help explain this, or even better, tell me where I'm going
wrong
and how to avoid it in future?

Thanks,
Adam.

 

[Adam Bryce]

Ahh, gotcha. That article was really useful, and its suggestion of using CDec
around each value solved the problem.

Thanks very much to you both!

Adam.

It's a fact of life with computers (nothing specific to Access).

The same problem exists in base 10. For instance, 1/3 is 0.33333 repeating,
so that 1/3 + 1/3 + 1/3 ends up as 0.99999. 0.3 and 0.35 are fractions
(3/10 and 35/100 respectively), and the same issue happens trying to
represent them in base 2 (or base 16 or however you want to think of it)

Check what Luke Chung had to say in
http://www.fmsinc.com/tpapers/math/index.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The percentages aren't calculated - I'm typing in the values 0.35 etc, by
hand, and the form is just formatting them as %.

if I enter 0.3, 0.35, 0.35 and then check the value of int([total]*100),
it
comes out as 99.

If I enter 0.35, 0.35, 0.3, the value of int([total]*100) comes out as
100.

I guess it is a rounding error - I didn't realise Access was quite that
inaccurate!

Thanks anyway,
Adam

This has nothing to do with whether it is a string or not. It has to do
with
rounding.
When your percentages are calculated, it is almost a certainty some of
the
calculations will result in some decimal that is masked by your
formatting.
So that the whole may appear to by 100%, but in fact it is something like
100.0000000003 (for example). That, then, will not evaluate to true.
The
easy fix is to compare on the Int or the Fix of the total. That will
drop
the decimals and evaluate to 100%
--
Dave Hargis, Microsoft Access MVP


:

Hi,

I came across this when coding a form in Acc2002, and found I could
replicate it, but had no idea why it happens:

I create a form with four text boxes: A, B, C and Total
A - C have a default value of zero, and an afterUpdate event that calls
a
function within the form code:

If [total] <> 1 Then
[total].forecolor = 255 'highlight text (red)
Else
[total].forecolor = 0 'normal text (black)
End If

The Total Box has a control source of:

=cdbl([A])+cdbl()+cdbl([C])

all the boxes are formatted as percentage*, with no d.p. - the idea
being
that if they don't sum to 100%, the user is warned by the total turning
red.
*I've tried it without formatting, and it doesn't change the problem at
all.

My problem is that, with certain combinations of A, B and C, the box
turns
red even if the values sum to 100.

For example having A-C being 30%, 35%, 35% respectively results in
'100%'
displayed in red. However, A-C entered as 35%, 35% and 30% results in
'100%'
displayed in black.

Having checked the code, it turns out that in the former case, the
value of
[total] is being returned as "1" instead of 1.

I've worked around this in my real code by just changing the condition
to <>
"1", but I'm still perplexed as to why the order of the numbers in the
expression changes the type of the result.

Can anyone help explain this, or even better, tell me where I'm going
wrong
and how to avoid it in future?

Thanks,
Adam.