need a Access query help?

Question

Here's my situation. I have student enrolled. I need to select the student's that were enrolled between to different dates (ex: Sept 1 - Sept 31). This needs to count how many days the student was enrolled during that time frame. This is to bill them for the classes. So reguardless if Johnny enrolled on Aug 15 and was still enrolled as of Sept 31, I need to list him and how many days enrolled in the time frame (31 day). If he enrolled on Sept 15, then it would be 16 days.

You can do SQL

Answer 1

This is for SQL server but the same logic will apply to Access.

select studentname, datediff(Day,max(enrolldate), min(enrolldate))
from ClassEnrolltable
where enrolldate> '2006-09-01 00:00:00.00' and
enrolldate < '2006-09-28 00:00:00.00'
group by sjobname

Answer 2

hey perry, for dates you must use "#":
select studentname, datediff(Day,max(enrolldate), min(enrolldate))
from ClassEnrolltable
where enrolldate> #2006-09-01 00:00:00.00# and
enrolldate < #2006-09-28 00:00:00.00#
group by sjobname

Answer 3

Look for "expression builder" in Access help.

What does your SQL look like so far?
You HAVE actually tried to generate the SQL, correct?