Percent Format Type

[plaguna]

Every time I calculate numbers to get a percent format type in a query It gives me a big number. For instance: 7689.43% instead of 76.89%. I tried every possibility in the Property Sheet and It doesn’t change the way I want. How can I change the Format defined? I have MS Access 2007. I’ll be grateful if anyone can give me some ideas.

Thank you in advanced

Plaguna

[Delerna]

Can you post the query and some data that is being used to calculate the percentage

[Stewart Ross]

As Delerna said, it helps if we see the calculation. I am sure, however, that the problem with your number formatting is that you are (correctly for what a percentage actually is) multiplying your ratio result by 100 - which Access and Excel annoyingly does over again in its percent formatting.

The percent format in Access, like Excel, expects percentages to be the result of taking the ratio of

Some Value / Some Total

not

Some Value / Some Total * 100

As mentioned, Access and Excel's percent formatting automatically multiply the result by 100 for display (but please note that the value stored remains unaltered).

For example, if Some Value is 1 and Some Total is 3, Some Value / Some Total = 0.33333... which will display as 33.33% using the percent format with two decimal places.

If you want to use the built-in percent formatting, don't multiply the result by 100 first.

-Stewart

[youmike]

At the risk of being controversial, I'd challenge Stewart's use of "annoyingly". The percent format is just what it says - a format or a way to portray underlying data, and not a method of calculating. It's up to developers to attend to the calculation niceties and most important to give specific guidance to any users of their systems as to precisely what must be input.

Access contain several Microsoft quirks - this is not one of them.

[plaguna]

This is what I’m trying to do:
Input,
Warranty Labor Sales = 47,709.00
Service Contract Labor Sales = 2,849.00
Warranty Labor Gross = 36,606.00
Service Contract Labor Gross = 2,321.00

Query percent calculation:
Total Warranty Labor Gross Profit %: ([Warranty Labor Gross]+[Service Contract Labor Gross])/([Warranty Labor Sales]+[Service Contract Labor Sales])

I was reading all about Numeric and Currency field format type, Format Defined, Custom format, conversion issues, etc. and, I found that what Stewart said is correct. Every time one uses a built-in percent formatting, (Numeric data type, and percent format) the number value is automatically multiplied by 100 and, a percent sign is added to the right (percent values displayed with 2 decimal places). Now, if one creates a percent calculation in a query using a built-in percent formatting, it has to be calculated without multiplying by 100, the otherwise it does it twice, and it will display 7699.47% instead of 76.99%. I tried it and It works.
Thanks everyone.

Plaguna