Microsoft KB Archive/259986

= How to select a date range without regard to year in Access 2002 =

Article ID: 259986

Article Last Modified on 8/11/2004

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q259986



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

IN THIS TASK
SUMMARY
 * The Challenge
 * The Solution

REFERENCES



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
 Open Access and create a new database.  Create the following table:   Table: tblTest --  Field Name: surveydate Data Type: Date/Time  Save the table as tblTest. Let Access assign a primary key. 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

 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]));

 On the View menu, click Design View.</li> On the Query menu, click Parameters.</li> If the parameters startDate and endDate are not already there, add them. Then set the Data Type box to Integer.</li> Save the query as qryRecordset.</li>  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 & &quot;0&quot; & strDay) Else convertDate = CInt(strMonth & strDay) End If End Function </li> Save the module as modConvertDate .</li> Create a new form in Design view.</li> Add the following five text boxes:

</li> Add a command button. Name the command button cmdQuery and give it the caption Run Query .</li>  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 & &quot;/&quot; _ & Me!txtDay1 & &quot;/2000&quot;)) strStop = convertDate(DateValue(Me!txtMonth2 & &quot;/&quot; _ & Me!txtDay2 & &quot;/2000&quot;))

Set db = CurrentDb Set qry = db.QueryDefs(&quot;qryRecordset&quot;) Set parmStart = qry.Parameters(&quot;startDate&quot;) Set parmEnd = qry.Parameters(&quot;endDate&quot;) parmStart.Value = strStart parmEnd.Value = strStop Set rst = qry.OpenRecordset

If Not rst.EOF Then rst.MoveFirst Me!txtResult = &quot;&quot; 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 </li> Save the form as frmTest .</li> Open the frmTest form in Form view, and then fill in the text boxes as follows:

</li> Click Run Query. Note that the txtResult text box returns three dates, as follows:

1/1/2000

1/7/2001

1/14/2002</li> 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</li></ol>

back to the top

<div class="references_section">