Percent Bug
By Richard Rost
2 years ago
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
Recommended Courses
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
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
Subscribe to Percent Bug
Get notifications when this page is updated
Transcript
Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we're going to talk about the percent bug. It's not really a bug; it's just a confusing way that Access handles numbers when you enter percentages. You'll see what that means in just a minute.
Today's question comes from Bryce in Lafayette, Louisiana, one of my Gold Members. Bryce says, "I think I might have discovered a bug in Access. I have a field to store interest rates, and it's set as a double with the format percent. If I type in 1.2, then it stores fine as 1.2 percent. However, if I type in point two, then I get 20 percent, which is not what I want. How do I fix this?"
Well Bryce, this all comes down to the way that Access handles data entry with percentages. So let me show you what I mean.
Here I am in my TechHelp free template. This is a free database. You can download a copy if you want to from my website. I'm going to go into my customer table and we're going to add "Interest Rate" down here, but not that far down. My mouse has a habit of not clicking where I put it sometimes, even if I don't click correctly. All right, "Interest Rate," and yes, it's a good mouse. I love those Corsair mice.
I'm going to make that a Number type Double. Remember, if you need floating point numbers (anything other than the integer types) you want to use Double. Not Decimal, not Single, always use Double. I have a whole separate video on Double versus Decimal coming out very soon.
For Format, we're going to put Percent in here. The number of decimal places is up to you; I'm going to leave it as Auto. Save it. Let's go into the Table View here. Scroll over to the right.
Here's my interest rate. Oops, someone's beaming in.
Now here's what Bryce is talking about. If I type in 1.2, I get 1.2 percent. That's perfectly fine. If I type in 5, I get 5 percent. If I type in .12, I get 12 percent. What's going on here? Let's try something else. 5 gives me 5 percent. What if I type in 0.5? I get 0.5 percent. If I type in .5, I get 50 percent. That's confusing. What's happening?
Here's what's going on. It's all about the way that Access is handling your data entry. If you start by typing in a digit (0 through 9), then Access automatically puts that percentage in there for you. If I type in a 1, it puts that 1 percent on the end. If I type in a 6, I get that 6. If I type in 0.5, I get it just fine. But if I start by typing in a decimal point, I don't get that percentage sign. That's just the way Access behaves. Note to the Microsoft team: Fix that. That's confusing for people. I get this email all the time. Just make it so that it either doesn't put the percent sign there at all, or if it sees a decimal it puts it there. Because if you type in .5 and then manually put the percent sign there, you're fine.
So the problem is that Access is not automatically adding that percent sign for you unless it sees a digit. If I type in 9, I get 9 percent. If I type in .9, I get 90 percent because it's treating it as a whole value. .9 being 9 tenths, or 90 percent.
If you want .09, there you go. Or you can just type in 9 percent or .09 percent. You can control it, but you have to either type in a digit first, meaning you have to type in 0.5 and not .5.
Be very careful with this. I ran into this problem myself. I was putting in dividend yields into my stock portfolio database, and I came across this problem. So you're not the first person to find this issue.
I showed you how to fix it by manual data entry. Let me show you another trick. This is more advanced, for the VBA folks, where you can do something in an AfterUpdate event in a form to get around this.
Let's close this.
All right, this is a little bit more advanced now. Let's go into the form here. Let's add that field to the form design. Go to form design, add existing fields, bring on "Interest Rate," drop it right down there. Maybe I'll slide it up here a little bit. Get a little format painter from this guy, drop it on there. Slide you over to the right. Make it look pretty, folks. Make your databases look pretty.
And I know this is at the bottom of the tab order, so let's fix the tab order real quick. Tab order, auto order. The interest rate is at the bottom and I don't want it looping to the next record, so let's go to the form properties. Go to Other, go to Cycle, and change it to Current Record.
Now if I come in here and I go, I'm on Credit Limit, I tab down and I want to do 20. So I could type in 20 and I get my percent. If I type in 0.2, notice the percent isn't put on there for me automatically. I still got my 20.
If I want 2, I can type in the 2 like that, or I could type in 2 percent manually. Don't put two percent signs in there.
But if I come in and I want 0.2 percent, if I type in 0.2, notice Access doesn't automatically put the percent sign on there. That's what happens. I was putting in a dividend yield the other day. It was like 0.6 percent. I typed in 0.6 and I got 60 percent. So how do we get around this at the form level?
We can use something called an AfterUpdate event. If you've never done an AfterUpdate event before, watch this video. It explains in a lot of detail about how it works.
We can go into Design View. Go to the Interest Rate field, go to Events, After Update, then the ellipsis. That's going to bring up my VBA editor, which opened up on my other monitor. I'll slide it over here.
In the Interest Rate AfterUpdate, here's what we're going to do.
Whenever you type something into a text box (a control on a form), the text box itself has a Value, which is the value in there, and it also has a Text property. The Text property is a string value indicating exactly what text is in there, which may or may not be the same as the value in the box.
What we're going to do is, when this thing gets updated, we're going to look at the Text property. We're going to see if the right-most character, the right one character, is a percentage sign. If it is, leave it alone. If it's not, we're going to divide that value by 100. In other words, if the percent sign isn't there, Access normally adds it for us if it sees that digit. If it doesn't see the digit, we want to assume the user didn't type it in and typed a decimal point first.
This is what it looks like:
If Right(InterestRate.Text, 1) <> "%" Then
InterestRate = InterestRate / 100
End If
That's it.
Let's see if it works. Save it (Control+S), close it, save it. Open it. Here we go. I'm going to start up here, tab down. I'm going to type in 5. I get my 5 and Access gives me that. Let's come back here. If I want 0.6, I type in 0.6. Access doesn't give me the percent sign, but if I hit tab, my code takes over. See, the code took over. It read what was in the box. It says, "Oh, there's no percent sign here. Let me divide that by 100 for you."
If I type in a 1, I get 1. If I type in 0.1, I get 0.1. If I type in 0.1%, it sees the percent sign and leaves it alone.
If I type in 500, and then delete the percent sign myself, I still get 500 percent.
So that's it. A lot of solutions in Access literally are one, two, maybe three lines of code tops. You don't need to be a monster programmer to make Access really powerful with just a little bit of code.
I'm going to copy that and put that in the description for you, so you have the code.
If you want to learn more VBA, you can start with my Intro to VBA video. This is a free video. It's on my website. It's on my YouTube channel. Go watch that.
This video will teach you more about the If Then statement. I have a video on those string functions, including that Right function. This one covers Left, Right, Mid, InStr, and a whole bunch of different stuff.
If you want to learn more about field properties, field sizes, percentages, formats, I cover that in my Access Beginner Level 3 class. I'm in the middle of re-recording this one right now for the latest version of Access. If you get a copy of this, you'll get the free upgrade when the new one is released.
If you want to learn how to program in Access properly from the beginning, start with my Access Developer 1 class. I go over everything you need to know in detail, in the order that it's supposed to be taught. So check that out.
So there you go, that's the percent bug. I know it's not really a bug. "Percent Bug" just makes a better headline. It's kind of a bug. I really think the Access team should modify that behavior. I think it should either never put the percent sign there for you or always put the percent sign there for you, regardless if you type in a decimal point. Change that.
Of course, you might be watching this two years in the future when they've already made this change and this video is completely moot.
But if you're having a problem and you don't understand why your interest rates aren't working, that's why.
There you go. There is your Fast Tip for today. I hope you learned something, and Bryce, I hope this helps you out. Live long and prosper, my friends. I will see you next time.
Quiz
Q1. What is the main issue discussed in the video regarding percentage fields in Microsoft Access?
A. Access does not allow percentages to be stored at all
B. Access can misinterpret the intended percentage based on how data is entered
C. Access automatically converts all percentage values to decimals
D. Access can only display whole number percentages
Q2. According to the video, what data type does Richard recommend for storing percentage data in Access?
A. Integer
B. Decimal
C. Double
D. Single
Q3. When entering a value in a Percent-formatted field, what happens in Access if you type a number starting with a digit, such as "1.2"?
A. It stores the value as 12 percent
B. It stores the value as 0.12 percent
C. It stores the value as 1.2 percent
D. It stores the value as 120 percent
Q4. What happens if you enter a value beginning with a decimal point, like ".2", into a Percent-formatted field in Access?
A. Access displays 0.2 percent
B. Access displays 2 percent
C. Access displays 0.02 percent
D. Access displays 20 percent
Q5. What is the underlying reason for the "percent bug" behavior in Access when entering numbers starting with a decimal point?
A. Access always interprets decimals as fractions of 1 percent
B. Access only adds the percent sign automatically if the entry begins with a digit
C. Access does not recognize decimal points in percentage fields
D. Access treats every entry as a whole number percentage regardless of format
Q6. According to the video, what is a reliable way to ensure the correct percentage is entered in a Percent-formatted field in Access?
A. Always type a leading zero before the decimal point (e.g., "0.2" instead of ".2")
B. Only enter numbers larger than one
C. Type the percent sign after any value entered
D. Only use whole numbers and avoid decimals
Q7. What is the purpose of using an AfterUpdate event in a form for percentage fields, as demonstrated in the video?
A. To automatically convert decimal values to text
B. To validate whether the field is numeric
C. To automatically adjust the value if it is entered without a percent sign
D. To prevent users from entering values above 100 percent
Q8. What does the VBA code snippet presented in the video do?
A. It multiplies entered percentages by 100 every time
B. It prevents input if the value is not a number
C. It checks for a percent sign and divides by 100 if not present
D. It sets the decimal places automatically based on the value
Q9. According to Richard, what is the key difference between the Value and Text properties of a textbox control in Access?
A. Value is the formatted display, Text is always raw data
B. Value is always text, Text is always numeric
C. Value holds the stored value, Text holds the user-typed string
D. There is no difference; they are always the same
Q10. What advice does Richard offer for those consistently entering decimal fractions as percentages in Access?
A. Enter all numbers as whole numbers only
B. Use a VBA script to handle conversion on the form
C. Always add a percent sign manually
D. Format the field as a string to avoid confusion
Q11. What is the MAIN recommendation to avoid confusion with percentage data entry in Access tables?
A. Always enter numbers less than 1
B. Always start data entry with a digit, not a decimal point
C. Never use the percent format for fields
D. Never enter zero as a value
Q12. Which of the following would correctly display as 0.6 percent in Access, according to the video and recommended practices?
A. Enter ".6" only
B. Enter "0.6" or "0.6%" and use AfterUpdate event if needed
C. Enter "60%" only
D. Enter "6" only
Answers: 1-B; 2-C; 3-C; 4-D; 5-B; 6-A; 7-C; 8-C; 9-C; 10-B; 11-B; 12-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone is all about the so-called "percent bug" in Microsoft Access. Although it is not technically a bug, it can be quite confusing the way Access handles percentage values in number fields. In this lesson, I want to clarify how this works, show you common pitfalls, and present some solutions both for manual data entry and using VBA.
Let me start by describing the problem. When you set up a field in a table in Access with the data type Double and use the Percent format, you might expect that typing numbers like 1.2 or 0.2 would always give you what you expect. However, if you enter 1.2, Access correctly stores and displays this as 1.2 percent. If you enter 0.2, depending on exactly how you type it, Access could interpret it as 20 percent or 0.2 percent, which is not consistent and creates confusion.
Access determines how to interpret your input based on whether you start with a digit or a decimal point. For example, typing "1.2" results in 1.2 percent and "5" gives 5 percent. Typing ".12" leads to 12 percent, because Access treats the number after the decimal as tenths and then multiplies by 100 for the percent format. The same applies if you enter ".5"; Access converts this to 50 percent. On the other hand, if you type "0.5", you will get 0.5 percent, which is what you might expect. So it all comes down to how you enter your data—whether you begin with a digit or a decimal point.
For instance, entering "9" will register as 9 percent, but ".9" gives you 90 percent. If you want just 0.9 percent, you should type "0.9". If you do not want to run into this issue, always enter a leading zero if the number is less than one, such as "0.5" for 0.5 percent. This ensures Access does not misinterpret your entry. I have run into the same issue myself, for instance, when entering dividend yields in a stock portfolio, so I completely understand how annoying this can be.
If you strictly follow correct data entry, you can avoid these mishaps, but errors are likely to happen, especially if other users do not know about this behavior. To provide an additional layer of protection, especially when entering data through forms, you can use a little VBA to help ensure correct values.
Let me explain how to implement this workaround in a form. First, add your Interest Rate field to the form in Design View, arrange it as you like, and set up the tab order and cycling so that data entry is smooth and does not automatically move you to another record if you do not want that.
Now, for the solution: in the AfterUpdate event of your Interest Rate field, use a simple VBA procedure to check whether or not the value entered ends with a percent sign. The Text property of the control holds exactly what the user typed into the text box. If the user did not put in a percent sign, and Access did not automatically add it, the underlying number might be much larger than intended. With a quick check in the AfterUpdate event, you can identify when the percent sign is missing and, if so, divide the value by 100 to set it correctly.
This AfterUpdate event method is simple and effective, requiring only a couple of lines of code. You do not have to be an expert programmer in VBA—just a basic understanding of If Then statements and string functions like Right will get you through. If you have never used AfterUpdate events before, I have resources that explain how to use them step by step. Similarly, my other tutorials cover the essential string manipulation functions you might need.
If you want to dig deeper into field properties, number formats, or how percentages are handled, these topics are covered in my Access Beginner Level 3 class. For those interested in learning VBA programming in Access from the ground up, my Access Developer 1 course is an excellent way to get started.
To sum up, this is how you deal with the "percent bug" in Microsoft Access. While it's not truly a software bug, it can certainly throw you off. I sincerely hope Microsoft makes the behavior more consistent in future versions. But for now, you have the tools and knowledge to handle it and prevent errors in your databases.
If you would like to watch a full video tutorial with detailed examples and step-by-step instructions covering everything discussed here, you can find it on my website at the link below.
Live long and prosper, my friends.
Topic List
How Access handles data entry for percent fields
Percent format behavior with different numeric entries
Results of entering percent values starting with a digit
Results of entering percent values starting with a decimal
Setting up a Double field with Percent format in a table
Manual correction of percent values during data entry
Adding a percent field to a form in Access
Fixing tab order and field cycling in forms
Using the AfterUpdate event to correct percent entries
VBA code to check for percent sign and divide by 100
Distinguishing between Value and Text properties in VBA
Ensuring correct percent values through AfterUpdate VBA
|
