Solved

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

Posted on 2007-11-13
9
1,018 Views
Last Modified: 2008-03-25
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.
0
Comment
Question by:Kell5000
9 Comments
 
LVL 27

Expert Comment

by:MikeToole
ID: 20278453
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
 
LVL 6

Accepted Solution

by:
Spot_The_Cat earned 500 total points
ID: 20278896
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
 

Author Comment

by:Kell5000
ID: 20281952
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
 
LVL 6

Expert Comment

by:Spot_The_Cat
ID: 20282148
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Kell5000
ID: 20282974
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
 

Author Comment

by:Kell5000
ID: 20283209
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
 
LVL 6

Expert Comment

by:Spot_The_Cat
ID: 20288402
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
 
LVL 1

Expert Comment

by:Computer101
ID: 21208276
Forced accept.

Computer101
EE Admin
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now