Iff Funtion!

[cap.zadi]

Hello, I am using the below Iff Function to compare results for alert msg.

=IIf(Nz([SValuePernt],0)=0 Or Nz([ActualPernt],0)=0,"N/A",IIf(Nz([ActualPernt],0)>=Nz([SValuePernt],0)*Nz([LCLAllowance],1) And Nz([ActualPernt],0)<=Nz([SvaluePernt],0)*Nz([UCLAllowance],1),"* The Actual Value is within Ctrl Range","* The Actual Value is out of Ctrl Range"))

Requirement:
Can we add field value in the alert message that how much it is variant either at UCL or LCL?

[ranman256]

If you're going to use that many IIf's, ...don't.
Use either a custom function,
or
a query and a lookup table.

[June7]

There's only 2 IIf's but I suppose would get more complicated to present more info.

Calculate the variances in other textboxes. Reference those textboxes in another expression. What if both are variant?

Your original expression could be a little simpler.

=IIf(IsNull([SValuePernt] - [ActualPernt]), "N/A", IIf(Nz([ActualPernt],0) BETWEEN Nz([SValuePernt],0)*Nz([LCLAllowance],1) AND Nz([SvaluePernt],0)*Nz([UCLAllowance],1), "* The Actual Value is within Ctrl Range", "* The Actual Value is out of Ctrl Range"))

Are other fields able to have data if SValuePernt or ActualPernt are Null? If Not, then Nz() function should not be needed.

[cap.zadi]

Hi

Thanks for your feedback.

Yeah this could be one of alternative to show variances in other other expression but i was looking forward if could be shown in "The Actual Value is out of Ctrl Range" line.

Woh, thats looks much simpler function. I apply this function. Most probally "SValuePernt or ActualPernt had rare chances to have zero values but may be UCL or LCL fields had zero values like "0.00" but cant be null.

[June7]

Could include variance(s), probably with more IIf's, or build a custom function.

Don't have to include all the calcs in one textbox. Some calcs might even be better done in query used as form or report RecordSource.

[cap.zadi]

Yeah,

U are right.

The variances are done in other text fields.

Thanks for shorter code.

regards