?
Solved

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

Posted on 2007-11-13
9
Medium Priority
?
1,069 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

649 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