NZ Help!

[Leslie W.]

Good Morning!

I have the following expression built:
OHPerc: NZ([SumIHours],0)/NZ([WorkHours],0)

In the properties I have it formatted to show ##%

My problem is sometimes the numerator is 0, and when it calculates, all I
see is %. I would like the formula to show me 0%, if possible.

I tried this IIF statement to no avail:
OHPerc:
IIf((NZ([SumIHours],0)/NZ([WorkHours],0))>0,(NZ([SumIHours],0)/NZ([WorkHours],0)),0)

Appreciate the help! Thanks!

 

[Steve]

If WorkHours is 0 or is Null your expression is going to bomb because you
can not divide by zero. So that needs fixed first. What do you want the
formula to show when WorkHours is 0 or is Null?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Leslie W.]

Hi, Steve. As a 0. What is your suggestion to fix the /0 problem. I'm
running into that. My first run of the report didn't have any data with 0 as
the denominator, but my new one does. If you could help, that would be
great. Thanks for the help!

If WorkHours is 0 or is Null your expression is going to bomb because you
can not divide by zero. So that needs fixed first. What do you want the
formula to show when WorkHours is 0 or is Null?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Good Morning!

I have the following expression built:
OHPerc: NZ([SumIHours],0)/NZ([WorkHours],0)

In the properties I have it formatted to show ##%

My problem is sometimes the numerator is 0, and when it calculates, all I
see is %. I would like the formula to show me 0%, if possible.

I tried this IIF statement to no avail:
OHPerc:
IIf((NZ([SumIHours],0)/NZ([WorkHours],0))>0,(NZ([SumIHours],0)/NZ([WorkHours],0)),0)

Appreciate the help! Thanks!

 

[John Spencer]

Try the following

OHPerc: IIF(Nz([WorkHours],0) = 0 , 0, Nz([SumIHours],0)/[WorkHours])


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Leslie W.]

John,

Actually, that's EXACTLY what I ended up doing! Thanks so much for your
advice!

Leslie

Try the following

OHPerc: IIF(Nz([WorkHours],0) = 0 , 0, Nz([SumIHours],0)/[WorkHours])


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Good Morning!

I have the following expression built:
OHPerc: NZ([SumIHours],0)/NZ([WorkHours],0)

In the properties I have it formatted to show ##%

My problem is sometimes the numerator is 0, and when it calculates, all I
see is %. I would like the formula to show me 0%, if possible.

I tried this IIF statement to no avail:
OHPerc:
IIf((NZ([SumIHours],0)/NZ([WorkHours],0))>0,(NZ([SumIHours],0)/NZ([WorkHours],0)),0)

Appreciate the help! Thanks!

 

[Steve]

How does that give you 0% when the nunerator is 0?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

John,

Actually, that's EXACTLY what I ended up doing! Thanks so much for your
advice!

Leslie

Try the following

OHPerc: IIF(Nz([WorkHours],0) = 0 , 0, Nz([SumIHours],0)/[WorkHours])


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Good Morning!

I have the following expression built:
OHPerc: NZ([SumIHours],0)/NZ([WorkHours],0)

In the properties I have it formatted to show ##%

My problem is sometimes the numerator is 0, and when it calculates, all
I
see is %. I would like the formula to show me 0%, if possible.

I tried this IIF statement to no avail:
OHPerc:
IIf((NZ([SumIHours],0)/NZ([WorkHours],0))>0,(NZ([SumIHours],0)/NZ([WorkHours],0)),0)

Appreciate the help! Thanks!