INF: How to Create a Formatted Entry Box for Percentage Values

Article ID: Q142228
Creation Date: 08-JAN-1996
Revision Date: 01-DEC-1996

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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

If you assign a field a Number data type with a Percent format, when you enter a value into that table or into a bound text box on a form, the value is multiplied by 100 and the percent sign (%) is appended to the end of the value. For example, when you enter "20," Microsoft Access displays the value as "2000.00%."

This article demonstrates how you can create a formatted entry box that enables you to enter a value as 20 and have Microsoft Access store the value in the table as 20.00%.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

To create a formatted entry box for percentage values, follow these steps:

Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).

Create the following new table and save it as Table1:

      Table: Table1
      -------------------------------------------------------------
      Field name: ID
         Data Type: AutoNumber (or Counter in Microsoft Access 2.0)
      Field name: Discount
         Data Type: Number
         Field Size: Single
         Format:  Percent

Create the following new form:

      Form: Form1
      ---------------------------------
      RecordSource: Table1
      DefaultView: Single Form

      Text Box:
         Name: Field1
         AfterUpdate: [Event Procedure]
      Text Box:
         Name: Discount
         ControlSource: Discount

Set the Field1 field's AfterUpdate property to the following [Event Procedure]:

      Sub Field1_AfterUpdate()
         Me!discount = Me!field1 /100
         Me!field1= Format(Me!field1 /100, "###.00%")
      End Sub

Set the form's OnCurrent property to the following [Event Procedure]:

      Sub Form_Current(Cancel as Integer)
         Me!field1 = Format(Me!discount, "###.00%")
      End Sub

Switch the form to Form View and enter a value in Field1. Press TAB to move to the next field. Note the formatting of the percentage value.
View the Table1 table in Datasheet view and note that the value is stored in the table in the same manner as it is displayed in the Discount field on Form1.

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.