IIf Criteria not working right 2

[blubomber]

I am trying to find out how many people have earned 10 or more points for a given day. If the earn the 10 or more points, i want a 2 to be the result, if not then a 0 or nothing.

If i use just >=10 in my query, i get the the expected number of results with the number of points showing. when i use IIf(>=10,2,0) then i get a smaller number of returns with all 0s when there should be a 2.

What i really want is that if points earned is equal or more than 10, then the result being a 2. If the criteria is not met, i dont want any return.

Any help is greatly appreciated.

 

[r937]

don't want any return" implies you don't want that row

use a WHERE clause for this

select person, 2 as result
from yourtable
where points >= 10

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[lupins46]

You need two things for this.

You need an extra column to show the value 2.
myresult:2

And you need your original criteria >=10 in the points column.

 

[blubomber]

Thank you for the quick response. I did forget to mention that i am using Access 2000 queries. would your suggestion still apply?

 

[blubomber]

Excellent! both of your responses were very helpfull. the suggestion by lupins46 was just what i was looking for.

Thank you again for the quick replies.