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

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 the percent sign in the Format statement, Microsoft Access multiplies the value by 100. For example, a bound numeric control with the Format property set to 0% returns 1200% if you type 12 in the control.

This article describes how to work around this behavior.

To display a number with a trailing percent sign, use one of the following two methods.

Method1

This method uses a custom function to return the formatted number:

1. Create a new module, and then type the following line in the Declarations section:
   Option Explicit
2. Type or paste 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])

Method 2

This method involves creating a query with a calculated field that corrects the error introduced when a percent sign is used in the Format statement. To use this method, follow these steps:

1. Start Microsoft Access and open any database
2. Create a table with the following structure:
   Table: Table1
   ------------------
   Field Name: Score
   Data Type: Number
   Field Name: MaxScore
   Data Type: Number
3. View the Table1 table in Datasheet view and type the following records:
   Score��� MaxScore
   -----------------
   20������� 40
   60������� 180
4. Create the following query based on the Table1 table:
   Query: Query1
   -----------------
   Field: %Score: Format([Score]/[MaxScore],"0.00%")
5. Run the query. Note that Field1 displays the correct value followed by the percent sign.

NOTE: The output of the Format statement is always a text string. The two methods listed here are useful for forms or reports where you display only the percentage value. This technique does not work in situations where you need to treat the value as a number. If you need the value to be represented by a number, do not use the Format statement. Instead, you can add a label control containing the percent sign immediately to the right of the text box control.

For more information about the Format function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type format function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.