"Between" not working in Calculated Field in Query 1

[Elysynn]

Hi Everyone,
I'm having a problem with a query not recognizing the "Between" portion of an IIf statement in a calculated field.

SELECT Query3.fk_ScorecardID, Sum(Query3.PctCallsHandled) AS SumOfPctCallsHandled, IIf([PctCallsHandled]>0.99,"E",IIf([PctCallsHandled] Between 0.75 And 0.99,"M","N")) AS OverallRank,
FROM Query3
WHERE (((Query3.Ranking)="M"))
GROUP BY Query3.fk_ScorecardID, IIf([PctCallsHandled]>0.99,"E",IIf([PctCallsHandled] Between 0.75 And 0.99,"M","N"));

The person I am currently working on has a PctCallsHandled value of 79%. Last time I checked, that fell firmly between 75% and 99%. However, it is evaluating it as not falling between those values - assigning an "N" in the OverallRank. I think I'm missing something really obvious here - because I swear I've done this very thing before and it has worked...

Let me know your thoughts or if I'm just plain losing my mind!

Thanks!
-Elysynn

 

[PHV]

As you've already tested against >0.99, why not simply this ?
IIf([PctCallsHandled]>0.99,"E",IIf([PctCallsHandled]>=0.75,"M","N")) AS OverallRank,


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Elysynn]

Hi PHV,
Simple solution... however, would you believe it is still returning the "N" value? I don't get it... =(

 

[Elysynn]

I figured it out... PctCallsHandled was being evaluated based on its individual values - not it's sum. If I changed the reference in the IIf statement to SumOfPctCallsHandled, I would get the right OverallRank, however it would prompt for a second SumOfPctCallsHandled parameter. I solved this by creating another query referencing the SumOfPctTotalCallsHandled field and placing my calculated field in the new query. It now works as it should - Between statement and all.

-Elysynn

 

[PHV]

And why not simply this ?
SELECT fk_ScorecardID, Sum(PctCallsHandled) AS SumOfPctCallsHandled, IIf(Sum(PctCallsHandled)>0.99,"E",IIf(Sum(PctCallsHandled)>=0.75,"M","N")) AS OverallRank
FROM Query3
WHERE Ranking="M"
GROUP BY fk_ScorecardID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Elysynn]

Ding! goes the lightbulb... I see the difference between what I was doing and what you have done... Indeed, your method works. Thank you PHV for teaching me a new trick... =) (And reducing my queries by one!!)

-Elysynn