Counting Records

[OBarry]

I am trying to create a query for a report that shows the total number of hours a volunteer has for specific classes, and between two given dates.

The query uses a table named "Schedule" that contains the Volunteer's ID Number, the Class Code, and the Date of the classes. (There are other fields in this table but that information isn't used in this paticular report).

In the query I am counting the Volunteer ID Numbers, with the Class Code equal to "xxx", between a range of dates that a user types in. (The reason why I am counting the ID Number is because each ID Number equals two hours). The query counts the ID Numbers per Class Code correctly **WITHOUT** the Date but I need to be able to specify a date range instead of getting the information for the whole Schedule table. With the date field in the query it doesn't return anything even though there are records with dates that are in the specified date range.

Does anyone know what I am doing wrong or why this won't work when a date range is specified? Thanks for any help or advice in advance.

 

[MTGMAN64]

I'm not sure in what sort of date format your class date field is in, but I would try this SQL statement to derive the information you need:

SELECT Count([Schedule.VolunteerID])*2 AS CountClassHours
FROM Schedule
WHERE ((([Schedule.ClassDate])Between #Date1# And #Date2#) And (([Schedule.ClassCode])=xx))
ORDER BY Schedule.VolunteerID;

This should give you a count of each volunteer date and then put it in VolunteerID order. Hopefully this is helpful to you.