counting days between dates with parameters...

M

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??
 
M

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?

KARL DEWEY said:
Try this --
Days:Datediff("d",IIF([EnrollDate] <[stdate], [stdate], [EnrollDate])
,IIF([DischargeDate] Is Null, [enddate], [DischargeDate]))

MonthlyReg said:
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??
 
J

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?

KARL DEWEY said:
Try this --
Days:Datediff("d",IIF([EnrollDate] <[stdate], [stdate], [EnrollDate])
,IIF([DischargeDate] Is Null, [enddate], [DischargeDate]))

MonthlyReg said:
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??
 
K

KARL DEWEY

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


MonthlyReg said:
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?

KARL DEWEY said:
Try this --
Days:Datediff("d",IIF([EnrollDate] <[stdate], [stdate], [EnrollDate])
,IIF([DischargeDate] Is Null, [enddate], [DischargeDate]))

MonthlyReg said:
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??
 
M

MonthlyReg

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

John Spencer MVP said:
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?

KARL DEWEY said:
Try this --
Days:Datediff("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??
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top