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

• 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 (\), which is 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:

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