Hi All,

I am trying to count the number of different students seen between two dates.

I have tried the following:

SELECT Count(*) AS Students
FROM (SELECT DISTINCT StudentID FROM tbl_TutorialRecords0708) AS T
WHERE (((tbl_TutorialRecords0708.Date) Between #9/1/2007# And #8/31/2008#));

this statement works fine without a date range clause in it but I really need to only count the number of students between these particular dates.

Is there anything wrong with my syntax?

When I run the above statement it brings up a parameter box saying 'tbl_TutorialRecords.Date' and then a space to enter.

Any ideas appreciated

:)

Try

SELECT Count(*) AS Students
FROM (SELECT DISTINCT StudentID FROM tbl_TutorialRecords0708 WHERE tbl_TutorialRecords0708.Date) Between #9/1/2007# And #8/31/2008#) AS T
;


The date range is applied in the subquery

Brian

Hi this didn't seem to work.

Went to run the query with this code and it said there was a syntax error.

Any other ideas?

Hi All,

I am trying to count the number of different students seen between two dates.

I have tried the following:

SELECT Count(*) AS Students
FROM (SELECT DISTINCT StudentID FROM tbl_TutorialRecords0708) AS T
WHERE (((tbl_TutorialRecords0708.Date) Between #9/1/2007# And #8/31/2008#));

this statement works fine without a date range clause in it but I really need to only count the number of students between these particular dates.

Is there anything wrong with my syntax?

When I run the above statement it brings up a parameter box saying 'tbl_TutorialRecords.Date' and then a space to enter.

Any ideas appreciated

:)

I offer two suggestions:
SELECT Count(*) AS Students
FROM (SELECT DISTINCT StudentID FROM tbl_TutorialRecords0708) AS T
WHERE (T.Date Between #9/1/2007# And #8/31/2008#);

AND

SELECT Count(*) AS Students FROM
(SELECT DISTINCT StudentID FROM tbl_TutorialRecords0708 AS T
WHERE tbl_TutorialRecords0708.Date Between #9/1/2007# And #8/31/2008#);


The first is based on the fact that the only "Table" referred to is the Table Alias "T" that is assigned to the query.
The Second is a correction to the syntax of the option provided by Brian

Either one should work.

OOPs! had a stray ) after tbl_TutorialRecords0708.Date, I would have thought that that would have been highlighted with the syntax error?


SELECT Count(*) AS Students
FROM (SELECT DISTINCT StudentID FROM tbl_TutorialRecords0708 WHERE tbl_TutorialRecords0708.Date Between #9/1/2007# And #8/31/2008#) AS T
;

Brian

OOPs! had a stray ) after tbl_TutorialRecords0708.Date, I would have thought that that would have been highlighted with the syntax error?


SELECT Count(*) AS Students
FROM (SELECT DISTINCT StudentID FROM tbl_TutorialRecords0708 WHERE tbl_TutorialRecords0708.Date Between #9/1/2007# And #8/31/2008#) AS T
;

Brian

All of us have the right to not be perfect all of the time. In fact I am sure that I have done worse than that. The important thing is that your option as modified is as good as mine.