Problem using SQL count property

[langals]

Trying to count a NumberOfRooms field in a table where records are between a start and an end date.

Coming back with an error saying that:

"Reservation.StartDate invalid because it's not contained in an aggregate function and there is no GROUP BY clause"

Here is the SQL code - could you tell me what is wrong with it

strSQL = "SELECT StartDate, EndDate, COUNT(NumOfRooms) AS RoomsOccupied FROM Reservation WHERE StartDate <= '" & strDate & "' AND EndDate >= '" & strDate & "'".

I have used the code without count and it finds the right records. Problem when I added the count feature.

Thanks

[moinkhan]

Change the query to
strSQL = "SELECT StartDate, EndDate, COUNT(NumOfRooms) AS RoomsOccupied FROM Reservation WHERE StartDate <= '" & strDate & "' AND EndDate >= '" & strDate & "'" & " Group By StartDate,EndDate".