I do this by creating a query (qryWkDates) based on the date field in my
database and having only that date field. I use the Query's Properties to
choose Yes next to Unique Values so that I only have one example of each
date in the db.
My form is called FrmChooseReport
The first (unbound) combo on my form, cboFromDate, is based on this query.
The second combo, cboToDate, has the RowSource left blank
In the Open Event of my form I have:
Private Sub Form_Open(Cancel As Integer)
Me.cboToDate.RowSource = "QryWkDates"
'cboToDate has all the dates in the query
'and you can use this combo alone
'to show all records preceding the date chosen in it
End Sub
The After Update Event of cboFromDate has
Private Sub cboFromDate_AfterUpdate()
Me.cboToDate.RowSource = "SELECT QryWkDates.WkDate FROM QryWkDates WHERE
(((QryWkDates.WkDate)>=[Forms].[FrmChooseReport].[cboFromDate]));"
Me.cboToDate.Requery
Me.cboToDate.Value = Null
'ensures that the results of a previous search are not shown
End Sub
I now have a button in the form which opens my report (based on the code
by
Allen Browne)
Private Sub cmbRptFullDetails_Click()
On Error GoTo Err_cmbRptFullDetails_Click
Dim stDocName As String
Dim Crit As String
Dim CritLength As Long
'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#"
'The format expected for dates in a JET query string.
stDocName = "RptFullDetails"
'based on code by Allen Browne
'
http://allenbrowne.com/
If Not IsNull(Me.cboFromDate) Then
Crit = Crit & "([WkDate]>=" & Format(Me.cboFromDate, conJetDate) & ")
AND "
'open all the data after or on the date in cboFromDate
End If
If Not IsNull(Me.cboToDate) Then
Crit = Crit & "([WkDate]<= " & Format(Me.cboToDate + 1, conJetDate) &
")
AND "
'open all the data before or on the the date in cboToDate
End If
CritLength = Len(Crit) - 5
If CritLength <= 0 Then
Crit = ""
'open report unfiltered
Else
Crit = Left$(Crit, CritLength)
'chop off last And
End If
DoCmd.OpenReport stDocName, acPreview, , Crit
Exit_cmbRptFullDetails_Click:
Exit Sub
Err_cmbRptFullDetails_Click:
MsgBox Err.Description
Resume Exit_cmbRptFullDetails_Click
End Sub
Evi
PJ said:
Hi All,
I want to create a text box that allow users to filter by date - from the
date entered first to most recent and/or by date range if possible. I tried
to search online but didn't see anything. Any leads would help. Thanks in
advance.
