Missing returned value in query.

[Shawn12]

I have an expression that I use to get a value based on a set of threshholds set in a table. These values are as follows:

ADH_TEAM_RATING_MIN ADH_TEAM_RATING_MAX ADH_TEAM_SCORE
0.001 0.8999 1
0.9 0.9299 2
0.93 0.9499 3
0.95 0.9599 3.5
0.96 0.9699 4
0.97 1 5


The expression is based on a percentage that is rounded to 2 digits that fall between these values. It is as follows:

RATING: Max(IIf([ADH_PCT1] Between [ADH_TEAM_RATING_MIN] And [ADH_TEAM_RATING_MAX],[ADH_TEAM_SCORE]))

Im not getting an error, the expression however returns nothing for 0.95. Any other value returns the accurate score. But 0.95 is NULL every time so 3.5 never shows up. Any help would be greatly appreciated TIA.

 

[Golom]

When you say "... But 0.95 is NULL ..." I assume that you mean that [ADH_PCT1] is NULL (obviously 0.95 isn't.)

NULL is of course, not between any two values, so the value of the third argument to IIF is used ... but you haven't supplied a third argument so NULL is returned for RATING.

Use [ignore][tt] ... [/tt][/ignore] tags to get the columns to line up like this
[tt]
ADH_TEAM_RATING_MIN ADH_TEAM_RATING_MAX ADH_TEAM_SCORE
0.001 0.8999 1
0.9 0.9299 2
0.93 0.9499 3
0.95 0.9599 3.5
0.96 0.9699 4
0.97 1 5
[/tt]

 

[Shawn12]

Sorry I didnt explain that right. When the returned value for [ADH_PCT1] is 0.95 nothing shows up, its just blank. Any other value like 0.94 and it say 3...as it should. There is no third argument. Thx for the [tt] tip.

 

[Golom]

It's probably a rounding issue with either [ADH_PCT1] or with the range bounds (i.e. the MIN or MAX values.) If the fields in your tables are floating point data types (Single or Double) then note that not every decimal value can be exactly represented by a floating point value.

Try converting them to scaled integer data types to do the comparison

RATING: Max(IIf([red]cDec([/red][ADH_PCT1][red])[/red]
            Between [red]cDec([/red][ADH_TEAM_RATING_MIN][red])[/red]
                And [red]cDec([/red][ADH_TEAM_RATING_MAX][red])[/red],
                    [ADH_TEAM_SCORE]
                    [red],and here's where the third argument goes[/red]))

... There is no third argument.

The IIF statement has the form

[blue]IIF (SomeLogicalTest, Result_If_TRUE, Result_If_FALSE)[/blue]

You have not included a "Result_If_FALSE" expression so it defaults to NULL which will display as a blank.

 

[Shawn12]

OK I think what I added for the 3rd argument solved the problem. Thanks.