Problem with percentage formatted field

[jonathanchye]

Hi,

I have a Field of Data Type Number which I have formatted to Percentage. I can't type any number below 100 though so for example if I want to input 11% it will not accept it and automatically converts it to 0%...

Anyone know whats wrong?

 

[John Big Booty]

Personally I've always found the percentage format to be confusing (Excel is the same), and never use it. I would simply store your value as a number, and simply divide it by 100 prior to using it in any calculations, there is some discussion on the subject here.

 

[vbaInet]

Do you have an Input Mask set?

 

[jonathanchye]

Do you have an Input Mask set?

I just want the % sign to show for users and no I don't have input mask set when I format it as percent.

Just thinking how would I automatically add the "%" sign to the reports generated from this table?

 

[vbaInet]

I clearly understood what you're asking. The question asked is part of debugging. Setting a format of Percent shouldn't stop you from entering a number less than 100.

Do you have anything in the Validation Rule property?

 

[jonathanchye]

I think I might have found the solution. You must set the Field Size to Single!

Credit to this thread though : http://www.accessforums.net/access/access-2007-percent-format-issue-3889.html

Hopefully it will help others with this issue in the future.

 

[jonathanchye]

Is it just me having this problem then? Created a new table to test just one percentage field and it won't let me enter any number below 100...

I am using MS Access 2010 with W7

On a side note I observed if I type something like "A&B" in a Title is would also autoformat it as "A_B" and flag it up for a rename...

 

[JANR]

On a side note I observed if I type something like "A&B" in a Title is would also autoformat it as "A_B" and flag it up for a rename...

Which is a good thing since a fieldname with a ampersand "&" in it is really bad, it has special meaning for access namely to concactionate string expressions together.

In newer Accessversion they have intrudused checking objectnaming to help you NOT to use bad names and Reserved words which in my opinion is an improvement.

JR

 

[vbaInet]

The Percent property multiplies the result by 100 to make it a true percentage. Have a look at this for workarounds:

http://support.microsoft.com/kb/208985

As for the A&B in the Caption property, it's not a problem because the ampersand (&) is used to create hot or short keys. If you put A&&B it will display the way you want.

 

[Simon_MT]

Avoid ampersands as it it a reserved character for the web. Even using an ampersand on web pages you have to use &.

Here is an example

http://www.trevorsutton.com/Originals_Groupings.aspx?Grouping=09TSP&OG=1

Simon

 

[Simon_MT]

Here is a dynamically caculated discount report control:

="Less " & CCur([Discount Percent])*100 & "% Discount"

Simon

 

[boblarson]

Percents are stored as amounts like

.05 for 5%

And if you have it set as Integer or Long Integer it can't store anything below 100% because they don't store fractions of whole numbers.

If you have the table set (as Single, for example) with format of percent the data needs to be entered as a fraction, so if you want 40% the number has to be set as .4

So, the divide by 100 can be useful. Also, if you do store it as a decimal like .4 then you can also (at least in Access 2003 or above) use this in code or a query:

FormatPercent([NumberField],2) Where the 2 is the number of decimals displayed after the percent sign. You could use Format([NumberField], "Percent") as well, but it doesn't have the follow on decimals. Or you can do it yourself Format([NumberField], "0.00")

So just a few other thoughts in there.