ACC: How to Format a Number with a Trailing Percent Sign
ID: Q119482
|
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
You cannot use a percent sign (%) in a format statement in Microsoft
Access. Microsoft Access does not recognize the backslash character (\)
(the normal switch to indicate a literal character in a format
string) when used in conjunction with the percent sign. If you do use it,
Microsoft Access will multiply the value by 100. For example, a bound
numeric control with the Format property set to "0%" would return "1200%"
if you entered "12" in the control.
This article describes how to work around this behavior.
MORE INFORMATION
One method of working around this behavior is to create a custom function
to return the formatted number. The following example demonstrates this
method:
- Create a new module and enter the following line in the Declarations
section:
Option Explicit
- Enter the following code in the module:
Function FormatNumber(Num)
FormatNumber = Format(Num, "0.00") & " %"
End Function
- Set the ControlSource property of a text field that you want to format
with the percent sign to the following expression:
= FormatNumber([Field1])
This technique is useful for forms or reports where you do not expect to be
editing the data. This technique would not work in situations where you
will be editing the data in the control bound to the numeric field.
Instead, you could add a label control containing the percent sign
immediately to the right of the text box control.
Keywords : kbusage GnlFrmt
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbhowto