Working with percentages in queries

[Guest]

I’m working with calculated fields that have percentages, but the data entry
is like 43.5% my problem is that in order to multiply and get related
percentage of a number I have to divide by 100. Access read the number as 435
and if I set up to divided by 1000 I still have the risk of having I wrong
answer in the case of the percentage been smaller. For example: 9.5% is 95 to
access and 43,5 is 435

 

[Allen Browne]

Paste the function below into a standard module.

Open your form in design view, right-click the text box where you want the
value to be interpreted as a percent, and choose Properties. On the Event
tab of the properties box, enter:
=MakePercent([Text1])
replacing "Text1" with the actual name of your text box.

The code examines the text in the field. If the user typed a percent sign,
it leaves the value alone, because Access will interpret that correctly. If
not, it will divide by 100.


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])

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