How do I count dates that fall between user directed start/end dates; for multiple fields?

Question

I want to create a form that will display the count of five fields that fall between a user directed start/end date.

In my table I have fields for date1, date2, date3, date4, date5 for each record.  When the form is called up I want the user to enter a start/end date, and then have the count of the associated dates to be displayed on the form.  So, between start and end there are x1 of date1, x2 of date2, x3 of date3, x4 of date4, and x5 of date5.

I had this functioning within an excel spreadsheet using a series of CountIfs, but I do know how to get this to work within Access.

Thank you.

Answer 1

Hi Mayhem65,

You had mentioned that,"I want to create a form that will display the count of five fields that fall between a user directed start/end date."

Please try to refer example below.

Date in Table (date_data):

Code:

Option Compare Database

Private Sub Command14_Click()
 Dim tmpRS As DAO.Recordset
 Dim db As DAO.Database
 Dim fld As DAO.Field
 Set db = CurrentDb
    For Each fld In db.TableDefs!date_data.Fields

         For Each ctlVar In Me.Controls
             If ctlVar.ControlType = acTextBox Then
            
                   If ctlVar.Name = fld.Name Then
                       Set tmpRS = CurrentDb.OpenRecordset("SELECT count(" & fld.Name & ") from date_data where CDate(" & fld.Name & ") between #" & Me.Text0 & "# and #" & Me.Text2 & "#;")
                    
                                 If tmpRS.RecordCount > 0 Then
                                     ctlVar.Value = tmpRS.Fields(0)
                                 Else
                                     ctlVar.Value = 0
                                 End If
                   End If
             End If
        
                        Set tmpRS = Nothing
         Next
        
    Next
End Sub

Output:

Note:- You need to create a textbox on a form with the same name of the field in table.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Hi Mayhem,

If you're looking for a Query solution, you might be able to use DCount() or a UNION query. For example:

SELECT DCount("*","TableName","Date1 Between #" & [Enter Start Date] & "# And #" & [Enter End Date] & "#") AS Date1, and so on...

Hope it helps...

Answer 3

Thank you.  I will let you know how I make out with this.

Mayhem.

---

Thank you, Mayhem65

Answer 4

Hi Mayhem65,

You had mentioned that,"I want to create a form that will display the count of five fields that fall between a user directed start/end date."

Please try to refer example below.

Date in Table (date_data):

Code:

Option Compare Database

Private Sub Command14_Click()
 Dim tmpRS As DAO.Recordset
 Dim db As DAO.Database
 Dim fld As DAO.Field
 Set db = CurrentDb
    For Each fld In db.TableDefs!date_data.Fields

         For Each ctlVar In Me.Controls
             If ctlVar.ControlType = acTextBox Then
            
                   If ctlVar.Name = fld.Name Then
                       Set tmpRS = CurrentDb.OpenRecordset("SELECT count(" & fld.Name & ") from date_data where CDate(" & fld.Name & ") between #" & Me.Text0 & "# and #" & Me.Text2 & "#;")
                    
                                 If tmpRS.RecordCount > 0 Then
                                     ctlVar.Value = tmpRS.Fields(0)
                                 Else
                                     ctlVar.Value = 0
                                 End If
                   End If
             End If
        
                        Set tmpRS = Nothing
         Next
        
    Next
End Sub

Output:

Note:- You need to create a textbox on a form with the same name of the field in table.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Deepak,

I am sorry, but I am having trouble understanding some of your notes....still new to access.

I have created a query that brings in the five date fields that I need.  I have the form created with the selection (text boxes) field for the start/end dates.

The form sources the query and I have fields on the form.

I presume I need this do loop to function for each field.  Where fld.name is the name of the date field.

What is CDate; MeText0; Me.Text2?  Any more assistance in guiding me through the variables would be very helpful.

Thank you for your patience.

---

Thank you, Mayhem65

Answer 5

The obvious solution with the current table would be a UNION ALL query:

PARAMATERS Forms!YourForm!txtDateFrom DATETIME,
Forms!YourForm!txtDateTo DATETIME;
SELECT "dt1" AS DateColumn, COUNT(*) AS DateCount
FROM date_data
WHERE dt1 BETWEEN Forms!YourForm!txtDateFrom
AND Forms!YourForm!txtDateTo
UNION ALL
SELECT "dt2", COUNT(*)
FROM date_data
WHERE dt2 BETWEEN Forms!YourForm!txtDateFrom
AND Forms!YourForm!txtDateTo
UNION ALL
SELECT "dt3", COUNT(*)
FROM date_data
WHERE dt3 BETWEEN Forms!YourForm!txtDateFrom
AND Forms!YourForm!txtDateTo
UNION ALL
SELECT "dt4", COUNT(*)
FROM date_data
WHERE dt4 BETWEEN Forms!YourForm!txtDateFrom
AND Forms!YourForm!txtDateTo
UNION ALL
SELECT "dt5", COUNT(*)
FROM date_data
WHERE dt5 BETWEEN Forms!YourForm!txtDateFrom
AND Forms!YourForm!txtDateTo;

Bind the form to the query, include the txtDateFrom and txtDateTo unbound controls in the form header.  In the AfterUpdate event procedure of each control requery the form with:

Me.Requery

However, having multiple data columns like this does cause me to suspect that you might be 'encoding data as column headings'.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.  A table structured like this would conform with the rule:

Date_Data
....Category
....CategoryDate

The table is 'all key', i.e. its primary key is a composite one of both columns.  The Category column would hold the dt1, dt2 etc values while the CategoryDate column would hold the date values.  The query would then be a simple count of the rows per category:

PARAMATERS Forms!YourForm!txtDateFrom DATETIME,
Forms!YourForm!txtDateTo DATETIME
SELECT Category, COUNT(*) AS DateCount
FROM Date_Data
WHERE CategoryDate BETWEEN Forms!YourForm!txtDateFrom
AND Forms!YourForm!txtDateTo
GROUP BY Category;

It would be a simple task to insert the current data into a correctly structured table by basing an 'append' query on a UNION ALL query.

---

Ken Sheridan, Stafford, England

Answer 6

Hi Mayhem65,

You had mentioned that,"I have created a query that brings in the five date fields that I need.  I have the form created with the selection (text boxes) field for the start/end dates. The form sources the query and I have fields on the form."

You can execute the query, after you get the start date and end date from user.

You can use the events of the controls placed on the form like textbox or button.

 Further, You had asked,"What is CDate; MeText0; Me.Text2? "

CDate is a type conversion function, I used it to convert text value to date.

MeText0 and Me.Text2 are the textbox on the form for Start date and End date.

dt1, dt2, dt3, dt4, dt5 are the textbox on the form one for each date field in the table.

When you enter the start date and end date and click the show count button, It will execute the code.

I use 2 For loops as nested loop.

First loop will loop through the fields in table and fetch the field name.

Then second loop will get execute and loop through the controls on form.

If field name and control name get match then it will fire a query and fetch the count for that specific field whose date value lies between start date and end date.

Display the count in the specific textbox.

Repeat the process for all fields.

I hope now you will get better idea about the logic for this example.

For better understanding, You can try to debug the code and check values on each step.

To get more information about Access Object Model, you can refer link below.

 Access Object Model Reference

Microsoft Access Object Model

Object model (Access VBA reference)

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 7

I have finally had a change to set this up.  I am, however, getting an error when I run it....I am likely missing something, but my trouble shooting has not returned a fix....

Private Sub CalcScoreCard_Click()
  Dim tmpRS As DAO.Recordset
  Dim db As DAO.Database
  Dim fld As DAO.Field
  Set db = CurrentDb
    For Each fld In db.TableDefs!ScoreCard_Query.Fields

      For Each ctlVar In Me.Controls
        If ctlVar.ControlType = acTextBox Then
      
          If ctlVar.Name = fld.Name Then
            Set tmpRS = CurrentDb.OpenRecordset("SELECT count(" & fld.Name & ") from date_data where CDate(" & fld.Name & ") between #" & Me.StartDate & "# and #" & Me.EndDate & "#;")
              
              If tmpRS.RecordCount > 0 Then
                ctlVar.Value = tmpRS.Fields(0)
                     
              Else
                ctlVar.Value = 0
              
              End If
          End If
        End If
            
            Set tmpRS = Nothing

      
      Next
    Next
End Sub

The error is occurring in "For Each fld In db.TableDefs!ScoreCard_Query.Fields"....I tried changing "TabelDefs" to the db name (front end), but it did not work.

Mayhem.

---

Thank you, Mayhem65

Answer 8

The error is occurring in "For Each fld In db.TableDefs!ScoreCard_Query.Fields"....I tried changing "TabelDefs" to the db name (front end), but it did not work.

Hi Mayhem,

Is   ScoreCard_Query   then name of a table or of a query?

If it is the name of a table you could try:

         For Each fld In db.TableDefs("ScoreCard_Query").Fields

Imb.

Answer 9

The error is occurring in "For Each fld In db.TableDefs!ScoreCard_Query.Fields"....I tried changing "TabelDefs" to the db name (front end), but it did not work.

Hi Mayhem,

Is   ScoreCard_Query   then name of a table or of a query?

If it is the name of a table you could try:

         For Each fld In db.TableDefs("ScoreCard_Query").Fields

Imb.

It is the query name which grabs the dates I am trying to count.   However, this did not work.

---

Thank you, Mayhem65

Answer 10

It is the query name which grabs the dates I am trying to count.   However, this did not work.

Hi Mayhem65,

Queries are not stored in TableDefs, only tables.

I never use QueryDefs (where queries are stored), so I am not sure of this, but what happens if you use:

        For Each fld In db.QueryDefs("ScoreCard_Query").Fields

Imb.

Answer 11

It is the query name which grabs the dates I am trying to count.   However, this did not work.

Hi Mayhem65,

Queries are not stored in TableDefs, only tables.

I never use QueryDefs (where queries are stored), so I am not sure of this, but what happens if you use:

        For Each fld In db.QueryDefs("ScoreCard_Query").Fields

Imb.

Thank you, but I am still getting an error....Run-time error '3265':  Item not found in collection.  (same error)

and at the same spot.

---

Thank you, Mayhem65