problem with sql code...can you see it?

I'm trying to run a query that figures total volunteer turnover between certain dates entered by a user. This is my code and and below that is the error it returns. By the way, I'm working with Access 2000.



Error:
You tried to execute a query that does not include the specified expression 'First(Volunteers.StatusNo)=4
And Count(Volunteers.StatusNo)>1 And Volunteers.IDate>=[forms]![Report Date Range]![Begin Date]
And Volunteers.IDate<=[forms]![Report Date Range]![End Date])' as part of an aggregate function.

However the query runs fine when I take out the
"
AND ((Volunteers.IDate)>=[forms]
![Report Date Range]![BeginDate]
And (Volunteers.IDate)<=[forms]![Report Date Range]![EndDate]));"

Thanks in advanced.

Brad

take the conditions that apply to individual rows out of the HAVING and put them into the WHERE clause

HAVING is only for conditions which apply to the group

also, if you're gonna group on something (StatusNo), then you pretty much have to include it in the SELECT list -- not because of syntax, you don't have to do it because of syntax, but because without it you will simply get a list of counts and you won't know which StatusNo they belong to

rudy

I guess if I explain exactly what I'm trying to do you might be able to understand if I'm doing it the best way.

Volunteers have different statuses (i.e. Prospect, Applicant, Active, Inactive, Leave of Absence) with dates related to them. StatusNo relates to the statuses (1, 2, 3, 4, 5) respectively. For instance, I might become active on 12/12/2003 and inactive on 12/30/2003. I'm trying to build a query that displays volunteer turnover from active to inactive in relation with between dates that a user inputs. But all I need is the total turnover number. So, the number of Active to Inactive between 12/12/2003 - 12/30/2003 is 20.

StatusNo Status
1 Prospect
2 Applicant
3 Active
4 Inactive
5 LOA

I am working out of the Volunteers table only. I have attached a diagram for reference.

Thanks for your insight.

if you use conditions in the WHERE clause to select only those rows you want, and if they all have the same status, then you don't need to GROUP BY

in effect, you allow the entire result set to become a single group, and you can still use aggregate fucntions like COUNT()rudy