Microsoft KB Archive/208985

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

Article ID: 208985

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q208985



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



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



MORE INFORMATION
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:

 Create a new module, and then type the following line in the Declarations section:

Option Explicit

  Type or paste 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])



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:

 Start Microsoft Access and open any database Create a table with the following structure:

Table: Table1

--

Field Name: Score

Data Type: Number

Field Name: MaxScore

Data Type: Number

</li> View the Table1 table in Datasheet view and type the following records:

Score    MaxScore

-

20        40

60        180

</li> Create the following query based on the Table1 table:

Query: Query1

-

Field: %Score: Format([Score]/[MaxScore],"0.00%")

</li> Run the query. Note that Field1 displays the correct value followed by the percent sign.</li></ol>

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.

<div class="references_section">