Demystifying Percentage Data Entry in Access

In this Microsoft Access tutorial I'm going to teach you about a peculiar way that percentages are handled when you enter them into your tables or forms. It may appear to be a bug but it's actually just the way Access works. I'll show you how to work around it, and we'll talk about a better fix for advanced users with a little VBA.

Bryce from Lafayette, Louisiana (a Gold Member) asks: I think I may have discovered a bug in Microsoft Access. I have a field to store interest rates and it sent as a double with format percent. If I type in 1.2 then it stores fine as 1.2%. However if I type in .2 then I get 20% which is not what I want. How do I fix this?

Links

• After Update: https://599cd.com/AfterUpdate
• String Functions: https://599cd.com/String
• If Then: https://599cd.com/IfThen
• Intro to VBA: https://599cd.com/IntroVBA

Recommended Courses

• Access Beginner 3: https://599cd.com/ACB3 - Field Sizes, Format
• Access Developer 1: https://599cd.com/ACD1 - VBA Programming

Workaround

This all has to do with how you enter the number into the field. If you start with a digit (0-9) then Access will automatically put the percent sign there for you. However, if you start with the decimal point it doesn't do that and therefore it's putting in .99 instead of .99%. The manual fix is either start by entering a number (not the decimal point) or manually type the % at the end.

VBA Solution

In the AfterUpdate event for your field (in this case, InterestRate):

If Right(InterestRate.Text, 1) <> "%" Then InterestRate = InterestRate / 100

Learn More FREE Access Beginner Level 1 FREE Access Quick Start in 30 Minutes Access Level 2 for just $1 Free Templates TechHelp Free Templates Blank Template Contact Management Order Entry & Invoicing More Access Templates

Resources Mailing List - Get emails when new videos released Consulting - Need help with your database Tip Jar - Your tips are graciously accepted Questions? Please feel free to post your questions or comments below or post them in the Forums.

Keywords

access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Percentage data entry, Bug or feature, Data entry workaround, Tips for data entry, Access formatting, VBA programming, percentage data entry, Percent sign format, Decimal point format, Double data type, AfterUpdate event, Fixing data entry errors