Format number field to show Percent

K

Kaylen

How do I format a field so whenever a number is enter, the field
automatically make that entry a Percent. I tried formattting the number to
Percent but when I entered for example 10, I get 1000%. How do I get the
field to show 10% when I enter 10? Thankyou for your help!!
 
J

Jeff Boyce

Kaylen

Take another look at how percents are calculated.

10 = 1000%! If you want to see 10%, enter .10 instead.

Or if you want to be able to enter "10" and have Access convert that to 10%,
you'll have to build a function that takes what you put in and divides by
100.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
P

Piet Linden

 How do I format a field so whenever a number is enter, the field
automatically make that entry a Percent. I tried formattting the number to
Percent but when I entered for example 10, I get 1000%. How do I get the
field to show 10% when I enter 10? Thankyou for your help!!
Click to expand...

How about entering 10% as .10? Otherwise you have to use the
beforeinsert event to divide by 100...
 
K

Kaylen

I would like to have the user enter 10 and it will just be 10%
What would be a code to put in After Update so that the number entered in
that field to be divided by 100?


Thanks
 
J

John W. Vinson

I would like to have the user enter 10 and it will just be 10%
What would be a code to put in After Update so that the number entered in
that field to be divided by 100?
Click to expand...

Private Sub controlname_AfterUpdate()
If Me!controlname < 1.0 Then ' don't change unless it needs changing
Me!controlname = Me!controname / 100
End If
End Sub
 
R

Rick Brandt

How about entering 10% as .10? Otherwise you have to use the
beforeinsert event to divide by 100...
Click to expand...

Isn't one of the features of 2007 that you can enter percentages without
the decimal point now?
 
K

Ken Sheridan

With interest rates going the way they are it would be rash to assume that
only values of 1% or more might be entered.

Ken Sheridan
Stafford, England
 
J

John W. Vinson

On Sun, 25 Jan 2009 10:14:03 -0800, Ken Sheridan

My suggestion will prevent values over 100% from being entered. I hope
interest rates don't go THAT high!
 
C

Clifford Bass

Hi Kaylen,

Just make your field a Number / Single field and set it's format to
Percent. For Access 2007: user just enters 10. Earlier versions: user
enters 10%. It will store as .1 and display as 10%.

Clifford Bass
 
K

Ken Sheridan

No it wouldn't. What it would do is convert anything entered as a fractional
value to 0.01 of the value, i.e. if 0.5 is entered it would be converted to
0.005 and format it as 0.50%; if 50 is entered (as the OP wants) it would
stay as 50 and be formatted as 5000.00%.

Did you have in mind?

If Me!controlname >= 1 Then ' don't change unless it needs changing
Me!controlname = Me!controlname / 100
End If

which would allow the user to enter values either as a fractional value or
as a value of 1 or more, so both 0.5 and 50 would represent 50%. But it
doesn't allow a percentage below 1 to be entered.

To "prevent values over 100% from being entered" put the following in the
control's BeforeUpdate event procedure:

Cancel = (Me.controlname > 100)

and in the AfterUpdate event procedure simply

Me.controlname = Me.controlname / 100

if entering percentages below 1 is to be allowed.

Ken Sheridan
Stafford, England
 
F

Fred

Mathematically, percent is a way of expressing a comparison between TWO
numbers, with a mathematical definition and symbology (% sign) to give it
context/definition. Or, a way to specify an operation to be performed on a
not-yet-specified number (i.e. 50% = multiply that not-yet-specified number
by .50). Percentages regarding changes in numbers need more context
explanation, i.e. "10% markup" can mean either multiply by 1.1 or divide by
..9, two different answers.

If there is going to be a mathematical answer, Kaylan will have to clarify
the question mathematically. Otherwise it can be anything, it's probably
just the entered number with a % sign appended to it, could even be just a
text field.

If 2007 enables this on a numeric field, it is only adding to the confusion.
 
K

Kaylen

Thank you everyone for your inputs and suggestions. For my database, the
percentages range from 0% to 100% only. I will try the code that John
suggested. Thank you for your expertise!
 
J

Jeff Boyce

Kaylen

Fred's comments remind me that percentages represent ratios.

If you store percentages, do NOT give in to the temptation to "do math" on
the percentages. After all, wouldn't it be simple to average 25%, 50% and
75% and come up with an average percentage of 50%? Yes, it would be easy.
No, it wouldn't (necessarily) be correct!

Since each percentage is one number divided by another, the ONLY way you
could legitimately average the percentages is if you were dividing by the
SAME number to calculate each percentage.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
K

Kaylen

I have used John's code to make the number divided by 100. But there is no
percent sign after the number. I would like to enter 10 and the value become
10%. Any suggestion?

This is the code I tried to put in the after update field of a number field:

Private Sub controlname_AfterUpdate()
If Me!controlname < 1.0 Then ' don't change unless it needs changing
Me!controlname = Me!controname / 100
End If
End Sub
 
C

Clifford Bass

Hi Kaylen,

Try this instead:

If Not IsNull(controlname) And Right(controlname, 1) <> "%" Then
controlname = controlname / 100
End If

Clifford Bass
 
K

Ken Sheridan

With Access 2007 I believe all you have to do is set the control's Format
property to Percent. With earlier versions if you set the Format property to
Percent and the user enters the percentage sign after the value, that's
enough. If you want the user to be able merely to enter the value and not
the percentage sign, but have this appear in the control, then read on:

1. To prevent values less than 0% or over 100% from being entered put the
following in the control's BeforeUpdate event procedure:

Const conMESSAGE = _
"Percentage must be between 0 and 100"

Dim ctrl As Control

Set ctrl = Me.ActiveControl

If Not IsNull(ctrl) Then
' cancel update an inform user if value
' entered otside permitted range
If ctrl < 0 Or ctrl > 100 Then
Cancel = True
MsgBox conMESSAGE, vbExclamation, "Invalid operation"
End If
End If

2. In the AfterUpdate event procedure put:

Dim ctrl As Control

Set ctrl = Me.ActiveControl

If Not IsNull(ctrl) Then
' if user has entered percent sign remove it
' before converting value
ctrl = Replace(ctrl.Text, "%", "")
' divide control value by 100
ctrl = ctrl / 100
End If

3. Set the control's Format property to Percent.

Note that the value is stored as a fractional value so 10% is stored as 0.1,
but by virtue of the formatting you see it as 10%. You need to be aware of
this when using the value in any calculations. If for instance this is a
discount applied to a price then you'd compute the discounted price with:

DiscountedPrice = Price * (1- Discount)

which, if the price is 10 GBP say and the discount 10% would evaluate to:

10.00 * (1 – 0.1)

i.e. 10.00 – 0.9

which is 9.00 GBP.

Ken Sheridan
Stafford, England
 
K

Ken Sheridan

Oops; should have been:

10.00 * (1 – 0.1)

i.e. 10.00 * 0.9

which is 9.00 GBP.

Ken Sheridan
Stafford, England
 
F

Fred

In light of the above discussions, the following two ideas might not be as
dumb as they sound:

- Put the % sign into the body of the form next to the field (i.e. not in
the field)

- Make it a text field and put code in to add a % sign to the end of
whatever you type in.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Test if value is less than percent 0
Percent problem again 7
Percent format in table 1
Access My Expression Contains Wrong number of arguments 0
Percent Format 10% shows 1000% 10
Curve fitting with weighted trendline 1
Format field as percent 1
Percent field folly 2

Top