Format number field to show Percent

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 procedu

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

"John W. Vinson" wrote:

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

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

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

"John W. Vinson" wrote:

On Fri, 23 Jan 2009 15:50:01 -0800, Kaylen
wrote:

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?

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

John W. Vinson [MVP]

--

John W. Vinson [MVP]

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.

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!

"Fred" wrote:

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.

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


"Kaylen" wrote in message
...
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!

"Fred" wrote:

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.

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


"Jeff Boyce" wrote:

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


"Kaylen" wrote in message
...
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!

"Fred" wrote:

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.

Hi Kaylen,

Try this instead:

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

Clifford Bass

"Kaylen" wrote:

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


"Jeff Boyce" wrote:

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


"Kaylen" wrote in message
...
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!

"Fred" wrote:

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.

Hi Kaylen,

Also, make sure the control uses the Percent format.

Clifford Bass

"Clifford Bass" wrote:

Hi Kaylen,

Try this instead:

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

Clifford Bass

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 procedu

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

"Kaylen" wrote:

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


"Jeff Boyce" wrote:

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


"Kaylen" wrote in message
...
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!

"Fred" wrote:

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.

Oops; should have been:

10.00 * (1 – 0.1)

i.e. 10.00 * 0.9

which is 9.00 GBP.

Ken Sheridan
Stafford, England

"Ken Sheridan" wrote:

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 procedu

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

"Kaylen" wrote:

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


"Jeff Boyce" wrote:

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


"Kaylen" wrote in message
...
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!

"Fred" wrote:

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.

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.