Problem with percentage formatted field (1 Viewer)

jonathanchye · Mar 29, 2011

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 · Mar 29, 2011

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 · Mar 29, 2011

Do you have an Input Mask set?

jonathanchye · Mar 29, 2011

vbaInet said:
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 · Mar 29, 2011

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 · Mar 29, 2011

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 · Mar 30, 2011

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 · Mar 30, 2011

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 · Mar 30, 2011

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 · Mar 30, 2011

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 · Mar 30, 2011

Here is a dynamically caculated discount report control:

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

Simon

boblarson · Mar 30, 2011

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.

Problem with percentage formatted field (1 Viewer)

jonathanchye

Registered User.

John Big Booty

AWF VIP

vbaInet

AWF VIP

jonathanchye

Registered User.

vbaInet

AWF VIP

jonathanchye

Registered User.

jonathanchye

Registered User.

JANR

Registered User.

vbaInet

AWF VIP

Simon_MT

Registered User.

Simon_MT

Registered User.

boblarson

Smeghead

Similar threads

Users who are viewing this thread