#Error on IIF Statement

[weilerdo]

Hi Guys, I have an IIF statement in one of my reports. The statement works great has long as there is data in the field [Text48]. If that field is blank then I get the #Error. I have tried several NZ or Is Null statements in it but all I get is that the statement is to complex then. What I need is to add a statement that if its blank then 0

=IIf([Text48]>97 And [Text48]<101,"A+",IIf([Text48]>92 And [Text48]<98,"A",IIf([Text48]>89 And [Text48]<93,"A-",IIf([Text48]>86 And [Text48]<90,"B+",IIf([Text48]>82 And [Text48]<87,"B",IIf([Text48]>79 And [Text48]<83,"B-",IIf([Text48]>76 And [Text48]<80,"C+",IIf([Text48]>72 And [Text48]<77,"C",IIf([Text48]>69 And [Text48]<73,"C-",IIf([Text48]>66 And [Text48]<70,"D+",IIf([Text48]>62 And [Text48]<67,"D",IIf([Text48]>59 And [Text48]<63,"D-",IIf([Text48]>0 And [Text48]<60,"F")))))))))))))

Thanks in Advance for looking at this

 

[pbaldy]

Can the value be over 101? If not, use the fact that Access will drop out after the first "True" test, so your tests are:

>97
>92
>89
etc.

You can also simplify with Switch instead of nested IIf() functions. To your question, perhaps:

IIf(IsNumeric(Text48), YourFormulaHere, "N/A")

 

[weilerdo]

Hey Paul, I am trying to follow the logic but am missing something most likely simple but, Im getting the error that the function contains the wrong number of arguments. Which I would assume has to do with the "N/A"


=IIf(IsNumeric(Text54),[Text54]>97,"A+",IIf([Text54]>92,"A",IIf([Text54]>89,"A-",IIf([Text54]>86,"B+",IIf([Text54]>82,"B",IIf([Text54]>79,"B-",IIf([Text54]>76,"C+",IIf([Text54]>72,"C",IIf([Text54]>69,"C-",IIf([Text54]>66,"D+",IIf([Text54]>62,"D",IIf([Text54]>59,"D-",IIf([Text54]>0,"F","N/A")))))))))))))

 

[pbaldy]

Your entire existing function would go in the true argument:

IIf(IsNumeric(Text48), IIf(...), "N/A")

You appear to have left off the first "IIf". Again, Switch() would be a little cleaner.

 

[weilerdo]

But once again Paul, you are correct.. I will definitly look at using the Switch function, I dont know that I have ever used it before.

 

[pbaldy]

Happy to help.

 

[Adam Naylor]

One issue that can often cause this (#Error) is when both parameters are being evaluated regardless of the result of the boolean expression :banghead:. I solved this with a custom function: adamjamesnaylor.com/2012/10/29/SQL-Server-Reporting-Services-IIf-Function-Evaluating-True-And-False-Parameters.aspx