Count rows of table between two dates for value in text box
Posted on 2007-11-13
I am creating a form for an Access 2003 Project. I need to calculate the days entered in the Academic Calendar table between two dates.
I have a two tables, AcademicCalendar and ClientSchoolContract. The AcademicCalendar table contains all of the scheduled school days for a school year, taking int account all school holidays and weekends. I need to calculate the number of school days for a school contract using a start date and an end date. The form is based on the table ClientSchoolContract.
I am able to generate the results manually in a SQL query in SQL Server using the following code.
SELECT COUNT(AcademicCalendar.AcademicCalendarDate) AS 'Total Days',
FROM AcademicCalendar CROSS JOIN ClientIsdContract
WHERE (AcademicCalendarDate >= ClientISDContract.ClientContractStartDate) AND
(AcademicCalendarDate <= ClientISDContract.ClientContractEndDate) AND
ClientISDContractID = 45
However, I need to recreate this action in the text box on the form that displays the total days after the user enters the start date and end date.
I also tried a couple versions of the DCount function as the Control Source for txtCountDays on the form and in the After Update event of the txtContractEndDate field.
DCount("AcademicCalendar.AcademicCalendarDate","AcademicCalendar","AcademicCalendarDate BETWEEN #" & Me.txtContractStartDate & "# AND #" & Me.txtContractEndDate & "#")
DCount("AcademicCalendar.AcademicCalendarDate", "AcademicCalendar", "AcademicCalendarDate BETWEEN Me.txtContractStartDate AND Me.txtContractEndDate")
This is my first Access Database using a SQL Server backend, so I am not sure if there are limitations on manipulating the data in the tables.