Darragh Murray

Ms Access – Percentage Field Formatting

—

by

Darragh
in

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.

Comments

68 responses to “Ms Access – Percentage Field Formatting”

  1. Brett Avatar
    Brett

    Thank you.

    You are a lifesaver. I have been pulling my hair out with this for two days!

  2. daz Avatar
    daz

    Brett. No worries. Glad I could be of assistance.

  3. John Avatar
    John

    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!

  4. daz Avatar
    daz

    John – no worries! Glad to be of service.

  5. SimpleSimon Avatar
    SimpleSimon

    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?

  6. daz Avatar
    daz

    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.

  7. ww1flyingace Avatar
    ww1flyingace

    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’.” 🙂

  8. daz Avatar
    daz

    No worries ww1flyingace.

  9. trader joe Avatar
    trader joe

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

  10. Debbie Dickinson Avatar
    Debbie Dickinson

    THANK YOU! Couldn’t find this anywhere and was also getting frustrated!

  11. daz Avatar
    daz

    Debbie, no worries. Thanks for stopping by.

  12. Stuart Turner Avatar
    Stuart Turner

    Thank you – just saved me a lot of headscratching!!

  13. daz Avatar
    daz

    No worries Stuart.

  14. Andres Avatar
    Andres

    Thank you! That problem has been bugging me for days!

  15. daz Avatar
    daz

    Glad I could help Andres

  16. LMB Avatar
    LMB

    Thank you – like you, I’ve fixed it before, but always forget to make the field single!

  17. Ian Avatar
    Ian

    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.

  18. daz Avatar
    daz

    No worries Ian!

  19. Louise Foley Avatar
    Louise Foley

    Wow, written 4 years ago and STILL helping people. Thank you, thank you!!

    1. Darragh Avatar
      Darragh

      Just shows that Microsoft still haven’t upped their game in their help files!

  20. Thierry Avatar
    Thierry

    Thank youuuuuuuuuuuuuuuuuuu!!!!!!!!!!!!!!!!!!!

  21. Will Avatar
    Will

    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.

    1. Darragh Avatar
      Darragh

      Glad I could be of help!

  22. Ross Avatar
    Ross

    Thank you! I agree with all previous comments — very frustrating, and such an easy solution!

    1. Darragh Avatar
      Darragh

      No worries, glad to have helped!

  23. Anonymoose Avatar
    Anonymoose

    You, sir, are a god among men, and have prevented at least one shooting spree/suicide.

    1. Darragh Avatar
      Darragh

      Ha, I’m pleased I’ve prevented that with this old post then!! 🙂

  24. Joyce Avatar
    Joyce

    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!

    1. Darragh Avatar
      Darragh

      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)

  25. Dimas Avatar
    Dimas

    Thank you for the tip, it’s been drivinbg me up the wall for last few days.

    1. Darragh Avatar
      Darragh

      No worries at all!

  26. Chrsitine Avatar
    Chrsitine

    Thank you so much. I was going nuts trying to figure it out!

    1. Darragh Avatar
      Darragh

      No worries. Seems that this bug is still around! Lots of people are looking this solution up.

  27. Martin Avatar
    Martin

    Gee, THANK YOU for your post. Would have taken forever for me to figure this out as an Access beginner.

    1. Darragh Avatar
      Darragh

      No, thank you for commenting 🙂 I’m really glad this little article is still helping people so long after I published it!

  28. Jeri Avatar
    Jeri

    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!

    1. Darragh Avatar
      Darragh

      Excellent Jeri, glad I could be of help!

  29. Geoff Avatar
    Geoff

    Thanks heaps! It’s been driving me insane…

  30. Karel Avatar
    Karel

    Very helpful, but I still have to divide my number by 100 to get it to display properly. I am working in Access 2010, just installed and I want to show .03%. m To get that I have to enter .0003. What am I doing wrong!

    1. Darragh Avatar
      Darragh

      Hi Karel, I’m not sure if I can help – I don’t have access 2010.

      However, what you have entered – .0003 is actually .03% though..I’m not sure if I understand whats wrong…

  31. John W Avatar
    John W

    Thanks for this, I was really getting frustrated at why i was entering 20% and it displayed 0% itn’t the www (and yourself) wonderful.

    1. Darragh Avatar
      Darragh

      Glad to be of help. The documentation is pretty rubbish surrounding this !

  32. Cathi Avatar
    Cathi

    Yes, this is still an issue in 2010 🙁 THANKS!

    1. Darragh Avatar
      Darragh

      That’s very handy to know, thanks Cathi.

  33. Dan Avatar
    Dan

    How do i calculate a % in an access 2010 form where it has to count both fileds w/ & w/o data until it reaches100%? In excel, it looks like this- =COUNTA(F3:F41)/(COUNTBLANK(F3:F41)+COUNTA(F3:F41)).

    thanks for any help you can provide.

    1. Darragh Avatar
      Darragh

      Sorry Dan, I’m not exactly sure what you’re asking – I’m guessing you’re talking about cumulative calculations? I don’t think Access have equivalent Counta or countblank functions either.

  34. Rebecca Avatar
    Rebecca

    More hair saved and not pulled out due to this 4 year old information 🙂 Thank you so much!! (Oh, and I have Access 2010 and the help is still no help as far as percentages go.)

    1. Darragh Avatar
      Darragh

      No worries Rebecca. Glad to have helped.

  35. Rebecca Avatar
    Rebecca

    Wow– actually almost 6 years old 🙂

    Thanks again!!

  36. Karen Avatar
    Karen

    Thank you so much. Very helpfull.

  37. Melanie Avatar
    Melanie

    GENIUS…you have saved my life, this has been doing my head in for days

    1. Darragh Avatar
      Darragh

      Glad to have helped.

  38. Zekkus Avatar
    Zekkus

    Thank you! The only one that suggest me the right solution!!!

  39. Gail Avatar
    Gail

    Thank you so much!! This small problem has taken me days to sort out.

    1. Darragh Avatar
      Darragh

      Glad to be of assistance!

  40. accesnew Avatar
    accesnew

    Thank you!!!!!

  41. STRH Avatar
    STRH

    Thank you!it was veryyyyyyyyyyyyyyy usefull.

  42. Keza Avatar
    Keza

    Echo thoughts of everyone above. Life saver! So quick to solve but would have taken me years on my own…. cheers.

    1. Darragh Avatar
      Darragh

      Ha no problems Keza! I’m super pleased (and somewhat surprised) this tip is still giving after all this time!

  43. Kevin Avatar
    Kevin

    Thank you! This was extremely helpful!

  44. Eric Avatar
    Eric

    As if you haven’t heard this already…but thank you! Saved me on a class project today. It’s amazing how simple this was once you pointed it out.

    1. Darragh Avatar
      Darragh

      No worries Scott 🙂

  45. Al Avatar
    Al

    2010 version of Access.
    I’m entering data as a percentage.
    Access is taking the whole number, rounding to an integer, then giving me that as a percentage. Eg. 2.5 becomes 200%. I’m new to Access, but have played with the Data Type and Format, which I’ve set to Number and Percent, respectively.
    I realise it’s been more than 7 years since you posted about this glitch, but I’m hoping you’re still out there in cyber land.

    1. Al Avatar
      Al

      OK, got it, cheers for the guidance.
      I just needed to find out how to set the Field Size…
      For anyone else looking, it’s here:
      office.microsoft.com/en-001/access-help/set-the-field-size-HA010341996.aspx#BM2

    2. Darragh Avatar
      Darragh

      I am – you have to set the format to ‘single’ – that’s the key part if you want decimals or percents to stop the rounding.

  46. Mauri Avatar
    Mauri

    My case is slightly different. The number (e.g. 12%) appear correct as 12 in the table, but wrong (1200%) in the form and report field.
    Both these fields are set as Percentage without decimals, the one in the table as Single.

    Any idea?

    1. Darragh Avatar
      Darragh

      What version are you using of access? Does it work when you put in 0.12?

      I’ve just tried it in 2007 – modelling my table exactly the way you’ve suggested and entered via datasheet and form and both seem to work out fine – it shows 12%.

      1. Mauri Avatar
        Mauri

        Access version is 2007, but is possible that the database has been created with a previous version, and then converted to 2007. Don’t know if that could be relevant.
        Anyway, if i put 0,12 in the form, the table shows 0,0012

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.