Conditional iif statement not returning desired values

[JimMinard]

Hello,
I need help with an iif statement, =IIf([OverAllScore_2]>=85,"A",IIf([OverAllScore_2]>=70,"B",IIf([OverAllScore_2]>=50,"C",IIf([OverAllScore_2]<50,"D"))))


I am trying to assign a letter grade based on the value of [OverAllScore_2]... No matter what the value of [OverAllScore_2] is, the statement only returns a "D" for the letter grade. Any ideas on what is wrong?

[Micron]

Usually, an IIF statement has 3 parts, but when you nest them like this, your last one doesn't. I think I would write it as
=IIf([OverAllScore_2]>=85,"A",IIf([OverAllScore_2]>=70,"B",IIf([OverAllScore_2]>=50,"C","D"))) although not sure because 100 is greater than 50 so potentially it could return C for 100. However, it works for me as a textbox control source on a form.

Also, where are you putting/using this? You don't say if it's a query or form control or what.

[JimMinard]

Hi – Thanks for getting back to me. I tried putting it in a text box on a form just to see if it works. I tried your suggestion but no dice…same issue.

[JimMinard]

I also tried it in a calculated field in the data table... Same problem, it only shows the D

[Micron]

I tried putting it in a text box

That's of little help. You put it where in a control?? Control source? Default value?
The expressions or functions you can put in a table field are quite limited, and this isn't one of them so forget that idea.


I don't have a field named OverAllScore_2 so I have 2 textboxes on a test form; one named OverAllScore_2. The second textbox has the expression I posted.
While it may not relate to where your expression is being used (which by the way you didn't answer) it does seem to prove that it correctly evaluates as written.
These images seem to indicate that.

[ssanfu]

I would do this calculation in a query.......

[JimMinard]

I put a text box on the form and just tried it in a calculated field in the table... It still only shows the D.

[JimMinard]

Thanks Steve - Thats not quite hat I'm looking for unless I'm not interpreting your suggestion correctly.

[Micron]

Maybe you'll have to post your db as I don't seem to be making any progress this way. I've asked twice how/where you're putting it in a textbox, plus I've already stated that such an expression will not work in a table, yet you're repeating the same information.

[JimMinard]

I think I found the root of the problem. My calculated field was formated as % & not as a number. I should be able to carry on. Thanks for all your efforts. Jim

[JimMinard]

Hi Micron, I'm quite new at using ACCESS. Thanks for your patience. I was connecting the data to the text box through the Property Sheet and selecting the [OVERALLSCORE_2 field] through Control Source. It seems that the proplem was the fact that I had the data formatted as a percent & not as a number. Thanks for your help.

[Micron]

Glad you solved it. Good luck with your project.