Solved

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

Posted on 2007-11-13
9
1,033 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
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.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

920 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