Expression help

[LordNightwinter]

I'm trying to write an expression in access. The parameters are a little complex. Of course, since something is wrong in the code it always returns W in the column.

SPI Color: IIf([Schedule Performance Index (SPI)]=">.9 or <=1.1","G",IIf([Schedule Performance Index (SPI)]=">=.8","Y",IIf([Schedule Performance Index (SPI)]="<.8","R",IIf([Schedule Performance Index (SPI)]="<1.1","B","W"))))

Help!

 

[plog]

When expressions get that complex its time for a function in a module. When you use a function you get to use as many lines as you need and it makes finding your errors so much easier. I suggest you do that, pass it your field and have it return your value instead of trying to debugg this.

With that said, I think you have confused numbers and text. The way all your evaluations are set up now, is that your field is text.

Is ">.9 or <=1.1" and actual value for your field? I bet not. I bet you wanted to do mathematical comparisons on it, but by placing those quote marks you turned all your comparisons into text comparisons.

 

[LordNightwinter]

When expressions get that complex its time for a function in a module. When you use a function you get to use as many lines as you need and it makes finding your errors so much easier. I suggest you do that, pass it your field and have it return your value instead of trying to debugg this.

With that said, I think you have confused numbers and text. The way all your evaluations are set up now, is that your field is text.

Is ">.9 or <=1.1" and actual value for your field? I bet not. I bet you wanted to do mathematical comparisons on it, but by placing those quote marks you turned all your comparisons into text comparisons.

Fair enough. Yes, what I'm trying to do is read a column that would be anywhere from nothing to 1.1 or above. From that number I want another column to read one of the indicated colors. >.9 - =<1.1 would be Green, between .8 and .9 would be Yellow, anything below .8 is Red and anything above 1.1 is blue. Anything else would end up White.

It's complex and convoluted but that's how my company rolls. I'm actually supposed to be doing SharePoint designing not database work but whatever. lol :banghead:

 

[LordNightwinter]

I still need help. I can't get this durn thing to work in a module or an expression. I'm having problems wrapping my head around why it won't work.

 

[LordNightwinter]

Okay so I got the expression to work save one issue. I can't figure out how to specify a range. I need the red area to be between 0.8 and 0.9 to end up Y and everything .9 and above to be G. Any thoughts?

SPI Color: IIf([Schedule Performance Index (SPI)]<"0.8","R",IIf([Schedule Performance Index (SPI)]>="0.8" And <"0.9","Y",IIf([Schedule Performance Index (SPI)]>="0.9","G",IIf([Schedule Performance Index (SPI)]>="1.1","B","W"))))

 

[LordNightwinter]

Solved!

SPI Color: IIf([Schedule Performance Index (SPI)]<"0.8","R",IIf([Schedule Performance Index (SPI)] Between "0.8" And "0.9","Y",IIf([Schedule Performance Index (SPI)]>="0.9","G",IIf([Schedule Performance Index (SPI)]>="1.1","B","W"))))

 

[plog]

Probably not. It will never produce "B".