Problem with Displaying Percentage when percentage is 100%

[kev100 via AccessMonster.com]

I've got a text box on a form that has a calcuation in its control.


It calculates the precentage, then displays it in the easy to read % format
(e.g: .89 is 89 %, .02 is 2%, etc).


I've having a hard time getting stuff to show and may be being unnecessarily
complex.


Here is the formula:

I need to find the total % of Shoe sales of all sales items. There are only
3 sale items: shoes, shirts, pants.

The current fields are: shoes, shirts, pants

The formula is: =[shoes]/([shoes]+[shirts]+[pants]) * 100

...then, I just have another text box with a "%" sign next that figure to
show the figure as a easy-read %.

HOWEVER.......If shoes are 100% of sales (e.g. 4/(4+0+0)*100
......the text box is BLANK.

Is something off in my formula....or is there an easier way to do this?

Any advice appreciated.
Thanks

 

[Corey-g via AccessMonster.com]

DO you have the width set to only show 2 digits by chance? I can't think of
any other reason that it's not displaying - but I'm no MVP either...

HOWEVER.......If shoes are 100% of sales (e.g. 4/(4+0+0)*100
......the text box is BLANK.

Everything works fine for any other combination (2%, 50%, 99%, etc...),
whatt happens if you have 4 SHoe sales out of 9 (.44444444444444...)?

 

[Guest]

nothing wrong with the formula....so it must be the implementation of the
formula that has an error ....and the high probability is your 0 values...

are you sure they are 0 rather than null?? type in an actual 0 for both as
a sanity check....set them up to default to 0 rather than be an empty null if
this is indeed the case.....

 

[kev100 via AccessMonster.com]

That's it !

If any field is zero it is actually null.

I'm not sure where to correct this, since the source for this report is a
query (which is itself based on another cross-tab query).

But....here another oddity....

If in the formula: =[shoes]/([shoes]+[shirts]+[pants]) * 100

...any item is null (e.g. shirts OR pant).....the result will be blank.

As long as there are values in ALL, things work fine.....if any one is empty..
..null occurs and a blank results.

Can this be corrected by converting/negating the null issue IN the formula?
Something like:

=val([shoes])/(val([shoes])+val([shirts])+val([pants]))) * 100

...etc?

Thanks very much.

 

[J. Goddard]

Hi -

In many (if not all) cases where an MS Access expression contains a
null, the entire expression evaluates to null.

What you wat to use is the Nz function, which converts a null to a
user-specified value, in your case zero (presumably).

So, instead of [shoes], you use nz([shoes],0), which returns a 0 if
[shoes] is null. Same for the others.

But don't use nz([shoes]+[shirts]+[pants],0)! You'll get zero when any
one of them is null.

HTH

John

 

[kev100 via AccessMonster.com]

Bingo....

That did it....

Simply NZ-ing each field as said did it.

Getting clean 0.00% or 100.00% now (where appr).

Thanks VERY much !

 

[J. Goddard]

Hi -

just a quick caveat -

did you account for the case where [shoes], [shirts] and [pants] are all
0, if such a situation can occur?

John

 

[kev100 via AccessMonster.com]

Ha !

I had actually started to mention that in the last post....but did not want
to become like company after 3 days.

Indeed...if all are zero....that div#! error will displays (indicating
division by zero attempted).

What I did was to add a conditional statement that checks if the divisor in
the to-be-used formula is 0.

Basically....

Control: iif [the divisor]=0,0,[use the formula's result]

...There may be an easier way....but....I didn't want to be too much of a
slacker )

Thanks very much...