summing variables

[Guest]

I have 4 text boxes on a form and I am trying to add their values together
and make sure they aren't greater than 1 before updating the record.

All the fields on the form are identical. They are set up with format
"Percent" and 0 decimal places.

The table setup is the same way.

The weird thing is that I can have the following and it works fine:
ctl1 = .60
ctl2 = .10
ctl3 = .30
ctl4 = 0

or
ctl1 = .95
ctl2 = 0
ctl3 = 0
ctl4 = .05

but if I enter
ctl1 = .60
clt2 = .30
ctl3 = .10
ctl4 = 0
It doesn't think that totals 1. Here is an example of the code:

dim ctl1 as control, ctl2 as control, ctl3 as control, ctl4 as control
ctl1 = forms!F_main!ctl1
ctl2 = forms!f_main!ctl2
ctl3 = forms!f_main!ctl3
ctl4 = forms!f_main!ctl4

if clt1+ctl2+ctl3+ctl4 <>1 then
msgbox "Total does not equal 1"
else
'continue on
end if

Any ideas?

 

[Graham Mandeno]

Hi J

Are these numbers stored as floating point (single or double) data type?
What I suspect is happening is you are getting rounding inaccuracies from
the way the numbers are stored in binary form - computers don't have ten
fingers ;-)

Have you examined the resulting sum to see what it is? You will probably
find it is 0.9999999 or 1.000000001 or some such.

You can fix this one of two ways:

1. Change the fields to Currency data type. Currency uses scaled integers
so has no rounding inaccuracies, and will store numbers up to 4 decimal
places. You can still format as a percent.

2. Change your test to allow a margin of error:

If Abs(clt1+ctl2+ctl3+ctl4-1) > 0.00001 Then

 

[Marshall Barton]

I have 4 text boxes on a form and I am trying to add their values together
and make sure they aren't greater than 1 before updating the record.

All the fields on the form are identical. They are set up with format
"Percent" and 0 decimal places.

The table setup is the same way.

The weird thing is that I can have the following and it works fine:
ctl1 = .60
ctl2 = .10
ctl3 = .30
ctl4 = 0

or
ctl1 = .95
ctl2 = 0
ctl3 = 0
ctl4 = .05

but if I enter
ctl1 = .60
clt2 = .30
ctl3 = .10
ctl4 = 0
It doesn't think that totals 1. Here is an example of the code:

dim ctl1 as control, ctl2 as control, ctl3 as control, ctl4 as control
ctl1 = forms!F_main!ctl1
ctl2 = forms!f_main!ctl2
ctl3 = forms!f_main!ctl3
ctl4 = forms!f_main!ctl4

if clt1+ctl2+ctl3+ctl4 <>1 then
msgbox "Total does not equal 1"
else
'continue on
end if

Nothing obvious jumps out at me. Maybe it's a minor issue
with floating point precision? Try this:

If (clt1+ctl2+ctl3+ctl4 - 1) < .000001 then
msgbox "Total is really, really close to 1"