IFF Function and if the 2nd field value is zero.

[cap.zadi]

Dears



I am using this iff statement to compare the actual results [AIDSocketPU] with standards [SIDSocketPU] but facing a problem if the standard [SIDSocketPU] is field value is zero or else then still it shows the results as "failed" whereas it should not show up any results.

=IIf([AIDSocketPU]<=[SIDSocketPU],"Passed","Failed")

[ranman256]

are either value NULL?

IIf(nz([AIDSocketPU])<=nz([SIDSocketPU]),"Passed","Failed")

[CJ_London]

you need a switch or nested iif statement

iif(nz([SIDSocketPU])=0,"",IIf(nz([AIDSocketPU])<=nz([SIDSocketPU]),"Passed","Failed"))

[cap.zadi]

Well, thanks both are working fantastic and most probably Nested statement since any of the value can be Null. There is only one problem left which is
If the value of [AIDSocketPU] actual reading is zero then still it shows passed and in other all cases it works.
but i want the result field to be as "N/A" if any of the values AIDSocketPU] or [SIDSocketPU]) are both are zero.

Thanks for the efforts and
Kindly advise

[cap.zadi]

I hope to get the solution for this one above point. I would like to ask for your expertise if we want to use the criteria of + 0.5 variation and in an other case it will be -1%, +5% variation rather than Less than and equal in the above statement.

[CJ_London]

but i want the result field to be as "N/A" if any of the values AIDSocketPU] or [SIDSocketPU]) are both are zero.

so modify the statement

iif(nz([SIDSocketPU])+nz([AIDSocketPU])=0,"N/A",IIf(nz([AIDSocketPU])<=nz([SIDSocketPU]),"Passed","Failed"))

I hope to get the solution for this one above point. I would like to ask for your expertise if we want to use the criteria of + 0.5 variation and in an other case it will be -1%, +5% variation rather than Less than and equal in the above statement

please clarify with examples

[cap.zadi]

Hi Ajax

Thanks for time.

I tried this iif(nz([SIDSocketPU])+nz([AIDSocketPU])=0,"N/A",IIf(nz([AIDSocketPU])<=nz([SIDSocketPU]),"Passed","Failed")) but giving the same result. If [AIDSocketPU] is null or Zero still shows that it is passed.

Kindly advise.

-------------------------

a. If the values of [AIDSocketPU] are -1%, +5%within the range of [SIDSocketPU] values then it is passed otherwise it is failed. (Null/Zero values case to be applied here also).

b. If the values of [AIDSocketPU] are -5, +5within the range of [SIDSocketPU] values then it is passed otherwise it is failed.

[CJ_London]

only other variation to the code I can suggest is

iif(nz([SIDSocketPU],0)+nz([AIDSocketPU],0)=0,"N/A",IIf(nz([AIDSocketPU],0)<=nz([SIDSocketPU],0),"Passed","Failed"))

Otherwise it implies these fields are double with a very small value - e.g. 0.00001 (so not null or zero)

to test


iif(round(nz([SIDSocketPU],0),1)+round(nz([AIDSocketPU],0),1)=0,"N/A",IIf(nz([AIDSocketPU],0)<=nz([SIDSocketPU],0),"Passed","Failed"))

[cap.zadi]

Hi

Thanks but still same results. May issues to select the proper format of the both fields? ;Like single, double or standard?

What about these:

a. If the values of [AIDSocketPU] are -1%, +5%within the range of [SIDSocketPU] values then it is passed otherwise it is failed. (Null/Zero values case to be applied here also).

b. If the values of [AIDSocketPU] are -5, +5within the range of [SIDSocketPU] values then it is passed otherwise it is failed.

[cap.zadi]

Hi

No, if the values of null/zero then must show empty cell or N/A.

[CJ_London]

just realised the suggested code will work if both fields are null so try this

iif(nz([SIDSocketPU],0)=0 or nz([AIDSocketPU],0)=0,"N/A",IIf(nz([AIDSocketPU],0)<=nz([SIDSocketPU],0),"Passed","Failed"))

right format - depends on what you want - do the values have decimals? if so, use double, if not then long or integer

re examples, you are providing a description and confusing, not an example

my best guess at what you want is something like

IIf(nz([AIDSocketPU],0)>=nz([SIDSocketPU],0)*0.95 AND nz([AIDSocketPU],0)<=nz([SIDSocketPU],0)*1.05,"Passed","Failed")

otherwise provide examples like this

AIDSocketPU..SIDSocketPU...Result....reason
.......1.................2................fail.... ..aidsocket<95% of SIDSocket
.......0.................3................N/A.....aidsocket is null or zero
.......4.................4................pass.... aidsocket+- 5% of SIDSocket
etc


use dots to maintain the columns as above and examples

[cap.zadi]

Hi Ajax

Beautiful. Its working fantastic now.
iif(nz([SIDSocketPU],0)=0 or nz([AIDSocketPU],0)=0,"N/A",IIf(nz([AIDSocketPU],0)<=nz([SIDSocketPU],0),"Passed","Failed"))

and for the 2nd i applied this which also worked fantastic.

=IIf(Nz([SIDSocketPU],0)=0 Or Nz([AIDSocketPU],0)=0,"N/A",IIf(Nz([AIDSocketPU],0)>=Nz([SIDSocketPU],0)*0.95 And Nz([AIDSocketPU],0)<=Nz([SIDSocketPU],0)*1.05,"Passed","Failed"))

In the 3rd one, want to use -1% instead of *0.95 and +5% instead of *1.05.

Like if the actual value [AIDSocketPU] of is in between -1% or +5% to standards value [SIDSocketPU] then it is passed otherwise failed.

[cap.zadi]

AIDSocketPU..SIDSocketPU...Result....reason
.......1.................2................Fail.... ..aidsocket<99% of SIDSocket (1 is 50% less than 2 so its failed since allowance is only -1%)
.......2.................1................Fail.... ..aidsocket<105% of SIDSocket (2 is 50% more than 1 so its failed since allowance is only +5%)
.......0/2.................3/0................N/A.....aidsocket/SIDSocket is null or zero
.......4.1.................4................pass.. .. aidsocket <105% of SIDSocket (4.1 is 102.5% of 4 so means its less than 5%)
.......3.98.................4................pass. ... aidsocket >99% of SIDSocket (3.98 is 99.75% of 4 so means its not less than -1%)

[CJ_London]

In the 3rd one, want to use -1% instead of *0.95

use *0.99 instead

[cap.zadi]

Ok then the 2nd one was for the % variation.

If the [SIDSocketPU] is 5+.5 units and actual value of [AIDSocketPU] is 5 units then it would be passed but if its 4.5 then again passed but if it is 4.4 then it would be failed and same at forward end as if the actual values are 5.5 passed and if it is 5.6 then it is failed.

How do you see this?