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
Solved

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

Posted on 2007-11-13
9
1,058 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

789 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