There are several ways to do this. How do you want the user to select the date ranges?
You could have two combo boxes on your form that you populate with dates from your table data (set Unique values to yes). The user selects a data from the start range, then the second combo cascades only later dates, and the user selects the end date from that combo. Then on your query, rewrite it to lool like this:
SELECT COUNT(ID) FROM MyTable WHERE SubmitDt BETWEEN [Forms]![myForm].[cboStartDate] AND [Forms]![myForm].[cboEndDate]
You can also do this if you want to prompt the user at runtime:
SELECT COUNT(ID) FROM MyTable WHERE SubmitDt BETWEEN #[Please enter a start date:]# AND #[Please enter an end date]#
You can also make a form that has two combos, one of which has Month Names, and one of which has years. The Month combo would have two columns, like this:
Then when the user selects the month and year, do this with the code:
Dim iMonth As Integer
Dim iYear As Integer
Dim sLink As String
Dim sDoc As String
If IsNull(Me.cboMonth) or Me.cboMonth = "" Then
MsgBox "Please select a month", vbCritical
iMonth = Me.cboMonth
If IsNull(Me.cboYear) or Me.cboYear = "" Then
MsgBox "Please select a Year", vbCritical
iMonth = Me.cboYear
sLink = "DatePart("m", [MyDateField] = " & iMonth " AND DatePart("yyy", [MyDateField]) = " & iYear
sDoc = "rptMyReport"
I may have gotten away from myself with that last one. That will work with SQL Server, but I am not sure if it will work with Acces. In that case, create two new fields in your query called: CurMonth and CurYear, and then put this in the Field:
And then in the criteria, refer to the combo on your form. The same for the year.
You can see there are many ways to do this.
Did that help?
Look it up at: http://wrox.books24x7.com