counting days between dates with parameters...

[MonthlyReg]

So, I'm trying to use a query to count the days of enrollment for multiple
students in a given month.
I have a parameter query set up to show which students were enrolled between
the parameter StDate and EndDate.
If a student is enrolled before the Parameter StDate and is discharged after
the parameter EndDate, then the Enrollement Days would be 31 (or 30 depending)
Now the trouble i'm running into is if a student enrolls half way through
the month, or is discharged from the program half way through the month then
the enrollment days would be less.
I have an EnrollmentDate Field and a DischargeDate Field to work with but I
don't know how?

Any ideas??

 

[MonthlyReg]

KARL, that worked like a charm other than it's missing a day. So when I want
to count the days of enrollment from Aug 1 - Aug 31, its only counting 30
Days. Any idea how I can fix this?

Try this --
Daysatediff("d",IIF([EnrollDate] <[stdate], [stdate], [EnrollDate])
,IIF([DischargeDate] Is Null, [enddate], [DischargeDate]))

So, I'm trying to use a query to count the days of enrollment for multiple
students in a given month.
I have a parameter query set up to show which students were enrolled between
the parameter StDate and EndDate.
If a student is enrolled before the Parameter StDate and is discharged after
the parameter EndDate, then the Enrollement Days would be 31 (or 30 depending)
Now the trouble i'm running into is if a student enrolls half way through
the month, or is discharged from the program half way through the month then
the enrollment days would be less.
I have an EnrollmentDate Field and a DischargeDate Field to work with but I
don't know how?

Any ideas??

 

[John Spencer MVP]

Add one.

DateDiff counts the number of transitions it takes to get from one time period
to another. The number of transitions from the first day of a period to the
last day of a period is one less than the number of days involved (Jan 1 to
Jan 31 takes 30 transitions)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

KARL, that worked like a charm other than it's missing a day. So when I want
to count the days of enrollment from Aug 1 - Aug 31, its only counting 30
Days. Any idea how I can fix this?

Try this --
Daysatediff("d",IIF([EnrollDate] <[stdate], [stdate], [EnrollDate])
,IIF([DischargeDate] Is Null, [enddate], [DischargeDate]))

So, I'm trying to use a query to count the days of enrollment for multiple
students in a given month.
I have a parameter query set up to show which students were enrolled between
the parameter StDate and EndDate.
If a student is enrolled before the Parameter StDate and is discharged after
the parameter EndDate, then the Enrollement Days would be 31 (or 30 depending)
Now the trouble i'm running into is if a student enrolls half way through
the month, or is discharged from the program half way through the month then
the enrollment days would be less.
I have an EnrollmentDate Field and a DischargeDate Field to work with but I
don't know how?

Any ideas??

 

[KARL DEWEY]

Daysatediff("d",IIF([EnrollDate] <[stdate], [stdate], [EnrollDate])
,IIF([DischargeDate] Is Null, [enddate], [DischargeDate])) +1

KARL, that worked like a charm other than it's missing a day. So when I want
to count the days of enrollment from Aug 1 - Aug 31, its only counting 30
Days. Any idea how I can fix this?

Try this --
Daysatediff("d",IIF([EnrollDate] <[stdate], [stdate], [EnrollDate])
,IIF([DischargeDate] Is Null, [enddate], [DischargeDate]))

So, I'm trying to use a query to count the days of enrollment for multiple
students in a given month.
I have a parameter query set up to show which students were enrolled between
the parameter StDate and EndDate.
If a student is enrolled before the Parameter StDate and is discharged after
the parameter EndDate, then the Enrollement Days would be 31 (or 30 depending)
Now the trouble i'm running into is if a student enrolls half way through
the month, or is discharged from the program half way through the month then
the enrollment days would be less.
I have an EnrollmentDate Field and a DischargeDate Field to work with but I
don't know how?

Any ideas??

 

[MonthlyReg]

Of course.... THANK YOU SO MUCH!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Add one.

DateDiff counts the number of transitions it takes to get from one time period
to another. The number of transitions from the first day of a period to the
last day of a period is one less than the number of days involved (Jan 1 to
Jan 31 takes 30 transitions)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

KARL, that worked like a charm other than it's missing a day. So when I want
to count the days of enrollment from Aug 1 - Aug 31, its only counting 30
Days. Any idea how I can fix this?

Try this --
Daysatediff("d",IIF([EnrollDate] <[stdate], [stdate], [EnrollDate])
,IIF([DischargeDate] Is Null, [enddate], [DischargeDate]))

:

So, I'm trying to use a query to count the days of enrollment for multiple
students in a given month.
I have a parameter query set up to show which students were enrolled between
the parameter StDate and EndDate.
If a student is enrolled before the Parameter StDate and is discharged after
the parameter EndDate, then the Enrollement Days would be 31 (or 30 depending)
Now the trouble i'm running into is if a student enrolls half way through
the month, or is discharged from the program half way through the month then
the enrollment days would be less.
I have an EnrollmentDate Field and a DischargeDate Field to work with but I
don't know how?

Any ideas??