I have an aggregate query (qry_progressivehealthlead
s) which is linked to a report (Rpt_PHIladder). In order to load, the query requires the user to enter a date range (for the field LeadDate). This is facilitated through the traditional basic Access messagebox/textbox popup.
I would instead like to have the user enter the date range via a form (frmWhatdates). A picture of the form is below.
To trigger the Rpt_PHIladder, a user would enter the date range, then click the button 'ladder'(Com_ladder) located on frmWhatdates.
This button currently has the following code behind it. Is there a way to substitute the date range the user enters into frmWhatdates, with the date range automatically asked by the query?
Private Sub com_ok_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "rpt_PHIMthly"
strField = "Leaddate"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere