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
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.
- On the Query menu, click Parameters.
- If the parameters startDate and endDate are not already there, add them. Then set the Data Type box to Integer.
- Save the query as qryRecordset.
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
- Save the module as modConvertDate.
- Create a new form in Design view.
- Add the following five text boxes:
Name Height txtMonth1 .2" txtDay1 .2" txtMonth2 .2" txtDay2 .2" txtResult 1.2" - Add a command button. Name the command button cmdQuery and give it the caption Run Query.
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
- Save the form as frmTest.
- 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 - Click Run Query. Note that the txtResult text box returns three dates, as follows:
1/1/2000
1/7/2001
1/14/2002 - 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
REFERENCES
For additional information about getting help with Visual Basic forApplications, click the article number below to view the article in the Microsoft Knowledge Base:
305326 OFFXP: Programming Resources for Visual Basic for Applications
Additional query words: inf acc2002
Keywords: kbhowto kbprogramming kbquery KB259986