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

  • 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.

    Monday, February 5, 2018 10:31 PM

Answers

  • 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 MSDNFSF@microsoft.com.


    Tuesday, February 6, 2018 2:23 AM
    Moderator
  • 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 MSDNFSF@microsoft.com.


    Friday, February 9, 2018 12:56 AM
    Moderator

All replies

  • 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 MSDNFSF@microsoft.com.


    Tuesday, February 6, 2018 2:23 AM
    Moderator
  • 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...

    Tuesday, February 6, 2018 2:55 AM
  • Thank you.  I will let you know how I make out with this.

    Mayhem.


    Thank you, Mayhem65

    Thursday, February 8, 2018 5:31 PM
  • 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 MSDNFSF@microsoft.com.


    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

    Thursday, February 8, 2018 6:25 PM
  • 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


    Thursday, February 8, 2018 7:05 PM
  • 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 MSDNFSF@microsoft.com.


    Friday, February 9, 2018 12:56 AM
    Moderator
  • 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

    Wednesday, February 14, 2018 7:02 PM
  • 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.

    Wednesday, February 14, 2018 7:16 PM
  • 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

    Wednesday, February 14, 2018 7:37 PM
  • 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.

    Wednesday, February 14, 2018 8:18 PM
  • 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

    Wednesday, February 14, 2018 8:23 PM