Overflow Error - 0 value

[Guest]

hello all...

I am 'attempting' to run a SQL with multiple expressions. I think I have
one expression giving me Overflow error because one of the fields has 0 as a
value. I am attempting to write the "Nz" function into the equation, but I
can't get it to work.

Can someone please help me with the following code. [NA KPI Master]![Lines]
contains the 0 values.

Thanks all!!!

Expr6: IIf((Nz([NA KPI Master]![Lines],0)=0,0),Sum((Nz([NA KPI
Master]![Lines]-[NA KPI Master]![SA Lines]),0)/[NA KPI Master]![Lines]))

 

[Guest]

Hi

I think you just have your ()'s in the wrong places and your Nz's look
incorrect. Also, you need to test each variable/field using Nz.

Try:
Expr6: IIf(Nz([NA KPI Master]![Lines],0)=0, 0, Sum((Nz([NA KPI
Master]![Lines], 0) - Nz([NA KPI Master]![SA Lines], 0))/[NA KPI
Master]![Lines]))

Cheers.

BW

 

[Guest]

Hi

Just spotted that you don't need the second Nz test for [Lines] as, if it is
Null, it would be picked up in the 'True' part of the IIf. Note that if [SA
Lines] cannot be Null then you can miss out it's Nz as well.

so, try:

Expr6: IIf(Nz([NA KPI Master]![Lines],0)=0, 0, Sum(([NA KPI
Master]![Lines] - Nz([NA KPI Master]![SA Lines], 0))/[NA KPI
Master]![Lines]))

Note that if [SA Lines] cannot be Null then you can miss out it's Nz as well:

Expr6: IIf(Nz([NA KPI Master]![Lines],0)=0, 0, Sum(([NA KPI
Master]![Lines] - [NA KPI Master]![SA Lines])/[NA KPI
Master]![Lines]))

BW

Hi

I think you just have your ()'s in the wrong places and your Nz's look
incorrect. Also, you need to test each variable/field using Nz.

Try:
Expr6: IIf(Nz([NA KPI Master]![Lines],0)=0, 0, Sum((Nz([NA KPI
Master]![Lines], 0) - Nz([NA KPI Master]![SA Lines], 0))/[NA KPI
Master]![Lines]))

Cheers.

BW

hello all...

I am 'attempting' to run a SQL with multiple expressions. I think I have
one expression giving me Overflow error because one of the fields has 0 as a
value. I am attempting to write the "Nz" function into the equation, but I
can't get it to work.

Can someone please help me with the following code. [NA KPI Master]![Lines]
contains the 0 values.

Thanks all!!!

Expr6: IIf((Nz([NA KPI Master]![Lines],0)=0,0),Sum((Nz([NA KPI
Master]![Lines]-[NA KPI Master]![SA Lines]),0)/[NA KPI Master]![Lines]))