Ms Access – Percentage Field Formatting
A quick tip to fix what shouldn’t be a problem!
Occasionally, Access can act rather confusingly. I ran into a typical example early this morning, when I was trying to get Access to allow entry of percentages correctly. I’ve done this about one thousand times before and probably solved this problem one thousand times as well, but every time it happens it causes me to scratch my head (mostly in disbelief).
What happens is this: You set up a field in a table, designate it to be formatted as percentage. Right OK – you go to your form design, set up your input fields, and then try to enter a number as a percentage, and notice that it keeps rounding to 100% or 0%. Frustration ++.
Back to table design. The problem here is that you have to correctly set the field data type as well as the data type format. However, the data type is not integer, double, or even decimal – it is usually single. Once you set that on your percentage fields, you can enter data in decimal format (i.e. 0.5, 0.25 etc etc) and it will show up in its proper percent format.
I know this is shouldn’t be a problem but, amazingly, people get caught by it all the time, and there doesn’t seem to be clear answers on the web. Hopefully this tip does something to addressing this issue.
Thank you.
You are a lifesaver. I have been pulling my hair out with this for two days!
Brett. No worries. Glad I could be of assistance.
Thank you!
I was really stuck on this on, hit this site on a Google search and Bingo, quick, easy and simple solution.
Thanks agein!
John – no worries! Glad to be of service.
But what if I want to enter “2.5″ on the form and for it to be displayed as “2.5%” and stored as “2.5%” in the table?
From memory, it might depend on your access version. Newer versions have figured this out (that when you say 2.5 in a percentage field, you mean 2.5% and not 250%). You could simply set it to percentage.
Thank you so much for this information! Why are points like this not given to us in the help section? How hard would it be to add the line “When dealing with percents, be sure that your variable and field are set to ‘single’.”
No worries ww1flyingace.
“I know this is shouldn’t be a problem but, amazingly, people get caught by it all the time, and there doesn’t seem to be clear answers on the web. Hopefully this tip does something to addressing this issue.”
So true. Thanks for the info.
THANK YOU! Couldn’t find this anywhere and was also getting frustrated!
Debbie, no worries. Thanks for stopping by.
Thank you – just saved me a lot of headscratching!!
No worries Stuart.
Thank you! That problem has been bugging me for days!
Glad I could help Andres
Thank you – like you, I’ve fixed it before, but always forget to make the field single!
Thank you!!!
Couldn’t find this answer anywhere and even in the “For Dummies” book, they don’t mention how to create percentage fields correctly. It is as if everyone ASSUMES that it is widely understood how to do this!!! For all your people explaining Access percentages out there… Start from how to create the actual data field in the design view… WHY doesn’t anyone (except daz here) explain this??? And Microsoft… seriously, fix this, or make a pop-up or something. GEEZ. Thanks Daz.
No worries Ian!
Wow, written 4 years ago and STILL helping people. Thank you, thank you!!
Just shows that Microsoft still haven’t upped their game in their help files!
Thank youuuuuuuuuuuuuuuuuuu!!!!!!!!!!!!!!!!!!!
Wow, thanks for this! Not only was this problem super annoying, the task of putting the problem into words on a search engine was tough as well! Your solution worked like a charm.
Glad I could be of help!
Thank you! I agree with all previous comments — very frustrating, and such an easy solution!
No worries, glad to have helped!
You, sir, are a god among men, and have prevented at least one shooting spree/suicide.
Ha, I’m pleased I’ve prevented that with this old post then!!
While I only suffered with this for a day, and not quite at the suicide/homicide point, I’m still very glad you posted the answer 4 years ago. And yes, it seems Microsoft is still expecting us all the “guess” at how things really work. So glad there are folks like you willing to share solutions!
Yeah, I’m pretty stoked to know this small post has proved so helpful to people over the years and still is helpful! I’m guessing this issue might still exist in Access 2010 then? (I haven’t used 2010)
Thank you for the tip, it’s been drivinbg me up the wall for last few days.
No worries at all!
Thank you so much. I was going nuts trying to figure it out!
No worries. Seems that this bug is still around! Lots of people are looking this solution up.
Gee, THANK YOU for your post. Would have taken forever for me to figure this out as an Access beginner.
No, thank you for commenting
I’m really glad this little article is still helping people so long after I published it!
Thank you so much! I couldn’t figure this one out. Very frustrating, until I found your easy answer: Change “field size” in Table Design View to “Single”. Voilà! Thank you and have a very Merry Christmas!
Excellent Jeri, glad I could be of help!
Thanks heaps! It’s been driving me insane…