How to select a date range without regard to year in Access 2002 (259986)

SUMMARY

This article shows you how to construct a query that returns all dates between and inclusive of start and stop dates without regard to year.

back to the top

The Challenge

Date values include the year. Therefore, you must write the query to ignore the year portion of the date.

back to the top

The Solution

1. Open Access and create a new database.
2. Create the following table:

      Table: tblTest
      ----------------------
      Field Name: surveydate
      Data Type: Date/Time
   					

3. Save the table as tblTest. Let Access assign a primary key.
4. Open the tblTest table in Datasheet view. Add some sample dates to the table, as follows:

   1/1/2000
   1/7/2001
   1/14/2002
   2/29/2000
   2/14/2001
   2/7/2002

5. Create a new query in SQL view that reads as follows:

   PARAMETERS startDate Short, endDate Short;
   SELECT [tblTest].surveydate, convertDate([surveydate]) AS calcValue
   FROM tblTest
   WHERE (((convertDate([surveydate]))>=[startDate] And
   (convertDate([surveydate]))<=[endDate]));

6. On the View menu, click Design View.
7. On the Query menu, click Parameters.
8. If the parameters startDate and endDate are not already there, add them. Then set the Data Type box to Integer.
9. Save the query as qryRecordset.
10. Create a new module, and then type or paste the following code:

    Function convertDate(inputField) As Integer
        Dim strMonth As String
        Dim strDay As String
        strMonth = Month(inputField)
        strDay = Day(inputField)
        If Len(strDay) = 1 Then
            convertDate = CInt(strMonth & "0" & strDay)
        Else
            convertDate = CInt(strMonth & strDay)
        End If
    End Function
    					

11. Save the module as modConvertDate.
12. Create a new form in Design view.
13. Add the following five text boxes:
    
    

    Name	Height
    txtMonth1	.2"
    txtDay1	.2"
    txtMonth2	.2"
    txtDay2	.2"
    txtResult	1.2"

14. Add a command button. Name the command button cmdQuery and give it the caption Run Query.
15. In the OnClick property, select Event Procedure. Click the Build button, and then type or paste the following code:NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.
    
    

       Dim strStart As String
       Dim strStop As String
       Dim strSQL As String
       Dim db As DAO.Database
       Dim qry As DAO.QueryDef
       Dim parmStart As DAO.Parameter
       Dim parmEnd As DAO.Parameter
       Dim rst As DAO.Recordset
    
       'Concatenate the date using any year.
       strStart = convertDate(DateValue(Me!txtMonth1 & "/" _
          & Me!txtDay1 & "/2000"))
       strStop = convertDate(DateValue(Me!txtMonth2 & "/" _
          & Me!txtDay2 & "/2000"))
    
       Set db = CurrentDb
       Set qry = db.QueryDefs("qryRecordset")
       Set parmStart = qry.Parameters("startDate")
       Set parmEnd = qry.Parameters("endDate")
       parmStart.Value = strStart
       parmEnd.Value = strStop
       Set rst = qry.OpenRecordset()
    
       If Not rst.EOF Then
          rst.MoveFirst
          Me!txtResult = ""
          Do While Not rst.EOF
             Me!txtResult.Value = Me!txtResult.Value & rst!SurveyDate & vbCrLf
             rst.MoveNext
          Loop
       End If
    
       db.Close
       Set qry = Nothing
       Set rst = Nothing
       Set db = Nothing
    					

16. Save the form as frmTest.
17. Open the frmTest form in Form view, and then fill in the text boxes as follows:
    
    

    Text box	Value
    Month1	1
    Day1	1
    Month2	2
    Day2	1

18. Click Run Query. Note that the txtResult text box returns three dates, as follows:
    
    1/1/2000
    1/7/2001
    1/14/2002
19. Change the value in the Day2 text box from 1 to 29, and then click Run Query. Note that the txtResult text box returns all six dates, as follows:
    
    1/1/2000
    1/7/2001
    1/14/2002
    2/29/2000
    2/14/2001
    2/7/2002

back to the top