Query Quirk: Criteria not working right

[Elysynn]

Hello,
I have a query conundrum. I am trying to do a very simple task of getting a count of agents with scores less than 85. I have the criteria set as <"85". Seems fairly straightforward, however the query returns all values less than 85 *and* everything equaling 100. I don't get it.

The average score field is derived from an expression in another query {AvgScore =([FieldA]/[FieldB])*100}.

Anyone have any ideas?

Thanks for your help and insight!
-Elysynn

 

[hneal98]

is this in a query? Can you paste the SQL version of the query here?

 

[Elysynn]

Yes, this is in a Query. Here is the SQL...

SELECT qryExceedMeetNeed.agent, Count(qryExceedMeetNeed.agent) AS CountOfagent
FROM qryExceedMeetNeed
WHERE (((qryExceedMeetNeed.AverageScore)<"85"))
GROUP BY qryExceedMeetNeed.agent;

let me know if anything additional is needed.

Thanks!

 

[lespaul]

are you sure average score should be a string and not a number?

what happens if you modify it to:

SELECT qryExceedMeetNeed.agent, Count(qryExceedMeetNeed.agent) AS CountOfagent
FROM qryExceedMeetNeed
WHERE (((qryExceedMeetNeed.AverageScore)< 85))
GROUP BY qryExceedMeetNeed.agent;

 

[PHV]

You may try this:
SELECT agent, Count(*) AS CountOfagent
FROM qryExceedMeetNeed
WHERE Int(AverageScore)<85
GROUP BY agent;

 

[Elysynn]

lespaul - I tried the modification, and I still got the same result. (I didn't think to try and remove the quotes in SQL - go figure... )

However, PHV, your method produced the correct result. Now if it isn't too much to ask, would you mind explaining why?

Thank you!
-Elysynn

 

[PHV]

Because qryExceedMeetNeed.AverageScore seems to be a text field you need to convert to numeric.

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

 

[Elysynn]

PHV Thanks for the explanation. Now it makes perfect sense...

-Elysynn