nested IIF statement

[anewlevel0419]

Hello,


I am having trouble with this nested IIF statement. I understand there is a lot going on with this, but IIF I can get this last condition, I will be all set.



=IIf([Primary Result 1]="ND","",IIf(Val([Primary Result 1])>=[MCL1],"O",""))


This portion works fine.



I want to nest one additional condition that states that if Primary Param 1 = pH and primary results is between the value of 6.5-8.5 then "", "O".


=IIf([Primary Result 1]="ND","",IIf(Val([Primary Result 1])>[MCL1],"O","",IIf([Primary Param 1]="pH" and [Primary Results 1] between 6.5 and 8.5,"","O")))


What this last condition is stating is that if the parameter selected is "pH" and the result value that is inputted is between 6.5 and 8.5 then the box stays blank otherwise the box shows a "O" to indicate that it is outside the limit. This is only specific to "pH" so the condition only needs to be true if primary parameter is "pH". There are 22 parameters, so I will copy paste this code throughout all the parameters.



Thanks,

Greg

 

[plog]

Time to build a function.

No point jamming a ton of logic into one line of code. Add to it that you need to use this code in 22 different spots and you need to create a new module and make a function to which you pass all the data it needs and returns the correct result:

Function get_Value(in_PrimaryResult1, in_MCL1, in_PrimaryParam)
  
  ret="Error"
' return value, by default is error

' put your logic here
' use as many lines as necessary
' no need to nest IIf statements


   get_Value= ret

End Function

Also, in that IIF code you posted you treat [Primary Result 1] as a string initially (="ND") and then as a number (between 6.5 and 8.5) that's bad practice and might trip you up. Which is it? Is that field text or numeric?


Lastly, the fact that you have a field called [Primary Param 1] is a little suspicious. Add to it that you said you have 22 parameters and I fear you might have an improperly structured table. How many [Primary Param X] fields do you have in your table?