The percentage format of a field when creating a table

[Guest]

Hi
I want to create a field that holds a percentage value. I select the percent option from the format field but it only comes in the form of 123.00%. I want it to contain a percent like 5%, not 500.00%.
Any suggestion
Sean

 

[James Goodman]

Yes. This is because the perct format multiplies the value by 100.

If you insert a new record after updating the column format you will find it
is correct.

You could change the format, & then divide the results by 100 in a query to
reset them to their correct values...



--
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/

Hi,
I want to create a field that holds a percentage value. I select the

percent option from the format field but it only comes in the form of
123.00%. I want it to contain a percent like 5%, not 500.00%.

 

[Allen Browne]

You can enter 5% or .05 to get the right result.

If you want Access to treat the value as a percentage even when the user
does not type the "%", use the AfterUpdate event of the control to divide by
100.

1. Paste this function below into a standard module and save:

Public Function MakePercent(ctl As Control)
If InStr(ctl.Text, "%") = 0 Then
ctl = ctl / 100
End If
End Function

2. To apply it to a text box named "MyRate", set its AfterUpdate property
to:
=MakePercent([MyRate])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want to create a field that holds a percentage value. I select the

percent option from the format field but it only comes in the form of
123.00%. I want it to contain a percent like 5%, not 500.00%.

 

[Guest]

I'm trying to achieve the same thing on a form, but no satisfaction so far. Firstly, what goes on the front of =MakePercent([MyRate]), to avoid a compile error? If I give it the name of the field in the source table, it compiles. But when I pass a value through the function module, it comes out wrong.

This is my AfterUpdate procedure attached to the input text box on the form

Private Sub ctlProjectPerCent_AfterUpdate(
ProjectPerCent = MakePerCent([ctlProjectPerCent]
Sto
End Su

And this is the module procedure

Public Function MakePerCent(ctl As Control
If InStr(ctl.Text, "%") = 0 The
ctl = ctl / 10
End I
End Functio

If I type 20 in the text box on the form, I expect to get 20% back. I get 20 going through to the MakePerCent function, converting to 0.02 at the end of the function. But it is passed back to the AfterUpdate procedure as 0, and it comes out as 2000.00% in the text box (which suggests that the value hasn't been passed back

It's a while since I did any VBA in Access, so I've probably made a stupid mistake somewhere. Any ideas?

 

[Van T. Dinh]

I am not sure of your set-up but there are a few problems with the posted
code:

1. You haven't declared the returned data type for your function
MakePerCent() which means that it will return a Variant by default. Use
strong typing whenever possible. This is not the problem, though.

2. You haven't assigned a returned *value* for the function either so the
function will *always* return Null regardless of what is in the Control.
Hence, I am not sure what you are trying to do with the function.

3. I think the statement "Stop" is not appropriate as used normally.

Check Access Help on the user-defined function and the Stop statement.

--
HTH
Van T. Dinh
MVP (Access)

I'm trying to achieve the same thing on a form, but no satisfaction so

far. Firstly, what goes on the front of =MakePercent([MyRate]), to avoid a
compile error? If I give it the name of the field in the source table, it
compiles. But when I pass a value through the function module, it comes out
wrong.

This is my AfterUpdate procedure attached to the input text box on the form:

Private Sub ctlProjectPerCent_AfterUpdate()
ProjectPerCent = MakePerCent([ctlProjectPerCent])
Stop
End Sub

And this is the module procedure:

Public Function MakePerCent(ctl As Control)
If InStr(ctl.Text, "%") = 0 Then
ctl = ctl / 100
End If
End Function

If I type 20 in the text box on the form, I expect to get 20% back. I get

20 going through to the MakePerCent function, converting to 0.02 at the end
of the function. But it is passed back to the AfterUpdate procedure as 0,
and it comes out as 2000.00% in the text box (which suggests that the value
hasn't been passed back.

It's a while since I did any VBA in Access, so I've probably made a stupid

mistake somewhere. Any ideas?