Count rows of table between two dates for value in text box

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.
Kell5000Asked:
Who is Participating?
 
Spot_The_CatConnect With a Mentor Commented:
Link the tables from SQLServer and try something like this in the AfterUpdate event.

Dim strSQL
strSQL = Me.dtEnd

strSQL = "SELECT COUNT(*) AS AcdCount " & _
        "FROM dbo_AcademicCalendar " & _
        "WHERE AcademicCalendarDate BETWEEN #" & Me.DtStart.Value & "# AND #" & Me.dtEnd.Value & "# " & _
        "AND ClientISDContractID = " & Me.ClientISDContractID.Value

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

With rst

    If (.RecordCount > 0) Then
        Me.TotDays = !AcdCount
    Else
        Me.TotDays = 0
    End If
   
End With
0
 
MikeTooleCommented:
You could convert the SQL to do the calculation in the Access front-end, but it's, imho, it's easier and better to continue doing the calculation in SQL Server and just sending the result to Access. ,

Take the SQL you already have and use it to create a User Defined Function - something like:

CREATE function [dbo].[ContractDays](@ContractID integer)
returns integer
as
begin
Declare @Days integer
SELECT   @Days = COUNT(AcademicCalendar.AcademicCalendarDate) ,
FROM AcademicCalendar CROSS JOIN ClientIsdContract
WHERE
(AcademicCalendarDate >= ClientISDContract.ClientContractStartDate) AND
(AcademicCalendarDate <= ClientISDContract.ClientContractEndDate) AND
ClientISDContractID = @ContractID
return @Days
end

You then use ADO in your Access front end to call the Function in the after update event of your form (I'm presuming here that the user-entered dates in Access are used to update the ClienISDContract table.
0
 
Kell5000Author Commented:
Mike,
Your answer is a little over my head.  How would I go about creating a function.  In the past I have created a couple in VBA using a module.  Would I do the same here?

Spot the cat,
I tried your solution, and received a run-time error.  Error 91: Object variable or with block variable not set.

Below is my code:

Dim strSQL As String
strSQL = Me.txtContractEndDate

strSQL = "SELECT COUNT(*) AS AcdCount " & _
            "FROM dbo_AcademicCalendar " & _
            "WHERE AcademicCalendarDate BETWEEN #" & Me.txtContractStartDate.Value & "# AND " & _
                "#" & Me.txtContractEndDate.Value & "#"

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

With rst

    If (.RecordCount > 0) Then
        Me.txtCountDays = !AcdCount
    Else
        Me.txtCountDays = 0
    End If
   
End With

I am getting the error on the line:
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

I am creating a new row in the table ClientISDContract with the entry of the dates so I cannot get the corresponding  ClientISDContractID until the record is stored in the table as ClientISDContractID is an autonumber type field.

Thanks for ya'll help!
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Spot_The_CatCommented:
Hi,

I've just cut and pasted your code back into Access this end and after changing field and table names  I'm getting no errors at all. The version I'm testing on is being run from an unbound form.

What version of Access  are you running and is the calling form bound to the dbo_AcademicCalendar table?

Also - the line 'strSQL = Me.txtContractEndDate' is not needed - it was a debugging line originally.

regards
Spot
0
 
Kell5000Author Commented:
I am working in Access 2007 but it is an Acess 2003 file.  I have it on my laptop while I get the bugs worked out so I can take it back and forth to work.

I don't have the form bound to the AcademicCalendar table.  I have it bound to the ClientISDContract table to collect the start date and end date.  I guess I could put the calculated textbox in a subform connected to the AcademicCalendar table and work it that way.

Kellie
0
 
Kell5000Author Commented:
I tried making a test form bound to the AcademicCalendar table and all of the text boxes were unbound.  I still got the same error on the same line of code.

Kellie
0
 
Spot_The_CatCommented:
Hi Kellie,

It may be a problem with ADO/DAO - try changing the 'set rst...' line to this:

dim rst as DAO.recordset

That should force it to use DAO.

Spot
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.