Re: fractions summing to a string instead of a number
- From: "Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx>
- Date: Thu, 17 May 2007 11:56:41 -0400
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
http://I.Am/DougSteele
(no e-mails, please!)
"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D208869E-E845-43F6-B0F9-892B74C1838E@xxxxxxxxxxxxxxxx
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
"Adam Bryce" wrote:
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([B])+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.
.
- Prev by Date: Re: 3 things...PLEASE help...CSV/QUERY/CHART
- Next by Date: RE: fractions summing to a string instead of a number
- Previous by thread: Re: Multiple CheckBox
- Next by thread: RE: fractions summing to a string instead of a number
- Index(es):
Relevant Pages
|
