Bizarre behavior with percentage fields !

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have several percentage fields that are causing endless problems. I
originally had them as numbers and divided by 100 for my calculations but the
user insisted on % signs on the forms so I changed them all to percent
fields. Now my problems have begun! I have 2 problems:
(1) Some of the fields when I click in the control on the form I see the
decimal value e.g. if I click on 17.50%, then it changes to .175 This only
happens in some controls even though they are all defined EXACTLY the same
way.
(2) I have to check that several of the fields on a form add up to 100%,
since they are percent fields they should actually add up to 1. The weirdest
thing is happening, they do add up to 1 (I displayed the individual values)
and I confirmed with a message box for the total but my IF test for <>1
SUCCEEDS!!!! I am using a SINGLE field to do the additions, is this the
problem. I tried using a DOUBLE field and that added up to something like
1.00000253 why I have not a clue. This only happens with one set of values
even though with another identical set of values the <>1 test FAILS.

Can anyone throw some light on all this???
 
The second issue has to do with how computers handle floating point numbers.
Just as 1/3 cannot be accurately stored as a decimal number, most fractional
values cannot be stored exactly as fractional binary (floating point)
numbers.

The solution might be to use a fixed point number such as Currency instead.
It might sound crazy, but try changing the Data Type of the field to
Currency, and set its Format property to Percent. The Currency type contains
exactly 4 decimal places, so a value such as 12.75% would be stored in
Access as exactly 0.1275, and the values should then add up correctly.

If the user forgets to type the % as part of the entry, Access interprets
12.75 as 1275%. You can change this by using the AfterUpdate event procedure
of the text box to see if there is a % sign in the Text property, and if
not, divide the value by 100. Copy the code below into a standard module.
Then you can just set the After Update property of the text box to:
=MakePercent([Rate])
where "Rate" represents the name of the text box.


Public Function MakePercent(txt As TextBox)
On Error GoTo Err_Handler
'Purpose: Divide the value by 100 if no percent sign found.
'Usage: Set the After Update property of a text box named Text23 to:
' =MakePercent([Text23])
'Author: Allen Browne

If Not IsNull(txt) Then
If InStr(txt.Text, "%") = 0 Then
txt = txt / 100
End If
End If

Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 2185 Then 'No Text property unless control has focus.
MsgBox "Error " & Err.Number & " - " & Err.Description
End If
Resume Exit_Handler
End Function
 
Back
Top