ACC: How to Format a Number with a Trailing Percent Sign

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.

One method of working around this behavior is to create a custom function to return the formatted number. The following example demonstrates this method:

1. Create a new module and enter the following line in the Declarations section:
   

         Option Explicit
   						

2. Enter the following code in the module:
   

         Function FormatNumber(Num)
              FormatNumber = Format(Num, "0.00") & " %"
         End Function
   						

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