How do I get Access to show percentages without the "-" sign?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rennie - 30 Sep 2005 18:33 GMT

I want percentages derived through a query and used in a report to show as
positive numbers. Access always displays percentages and negatives. how do I
change this?

Reply to this Message

Brendan Reynolds - 30 Sep 2005 21:39 GMT

There is, to the best of my knowledge, no known issue that would cause
Access to do this unless there is some error in the calculation. If you can
provide more information about how you are calculating percentages, perhaps
someone may be able to see what the problem is. I suppose if you're
convinced that the result is otherwise correct, you could use the Abs()
function to convert the negative value to its absolute value, but I would
not recommend it - it would be better to determine what the underlying
problem is.

Signature

Brendan Reynolds (MVP)

>I want percentages derived through a query and used in a report to show as
> positive numbers. Access always displays percentages and negatives. how
> do I
> change this?

Reply to this Message

Rennie - 30 Sep 2005 22:16 GMT

I'm using a yes/no in the table (the form for data entry is a simple
checklist). In the query on which the report is based I draw a percentage of
items checked for each group (I'm monitoring the completeness of submittals
from our sales force). For instance, a certain bit of info is either there
or it is not. If the salesman gets a check mark only 50% of the time the
query counts up the yes/no answers and spits out -50%. The minus sign carries
over into the report.

I hope that's clear - I'n not finding it to be the easiest thing to describe!

Thanks.

> There is, to the best of my knowledge, no known issue that would cause
> Access to do this unless there is some error in the calculation. If you can
[quoted text clipped - 9 lines]
> > do I
> > change this?

Reply to this Message

Linda Burnside - 01 Oct 2005 00:22 GMT

In the query which supports the form/report, change the field that contains
the yes/no data to IIf([MyYesNoField] = -1, 1, 0). This way, you'll get a
postive number instead of a negative number.

Linda

> I'm using a yes/no in the table (the form for data entry is a simple
> checklist). In the query on which the report is based I draw a percentage
[quoted text clipped - 30 lines]
>> > do I
>> > change this?

Reply to this Message

Brendan Reynolds - 01 Oct 2005 16:38 GMT

Internally, a Yes/No field stores the values -1 for True, and 0 for False,
so if you sum them, you'll get negative values (-1 + -1 = -2). You could try
Linda's suggestion using IIf(), or you could use the Abs() function (Abs(-1)
= 1) or if you may be able to use Count() instead of Sum(). For example,
using the Products table from the Northwind sample database as an example,
this query returns -8 ...

SELECT Sum(Discontinued) AS SumOfDiscontinued
FROM Products
WHERE Discontinued = True

... but this query returns 8 ...

SELECT Count(Discontinued) AS CountOfDiscontinued
FROM Products
WHERE Discontinued = True

Signature

Brendan Reynolds (MVP)

Reply to this Message