Filter by Year Month Start and Year Month End.

[CraigDouglas]

Please help. I have a report that shows the sales, rptMonthlySales. One of the fields is called YearMonth. I have two combo boxes on a form that have the year and month in them. They are called cboYearMonthStart and cboYearMonthEnd. Then I have a button which is called cmdOpenRptMonthlySales. I would like to put the code in the button so that it filters the report between the year month selected from the combo boxes. Thank you for any help.

 

[Gasman]

You have other reports using that criteria?, so what is different about that report?
I was going to suggest that you split year and month, but you have done that already, dupl;icating the data?

Bring in those other fields in the combo and use them in the filter of the OpenReport.
I would probably convert those to actual dates and then compare against your date field.
All those month/year fields can be calculated.

You could use DateSerial() to get start and end dates.
Tip: Using 0 as the day number, gives you the last day of the previous month.

 

[CraigDouglas]

You have other reports using that criteria?, so what is different about that report?
I was going to suggest that you split year and month, but you have done that already, dupl;icating the data?

Bring in those other fields in the combo and use them in the filter of the OpenReport.
I would probably convert those to actual dates and then compare against your date field.
All those month/year fields can be calculated.

You could use DateSerial() to get start and end dates.
Tip: Using 0 as the day number, gives you the last day of the previous month.

My other reports have the code in the on open I think it is called event. I was hoping to have the code work in the button and not have the code in the report. Then I can have fewer reports. The reports would get filtered by the button, so I could use the same report.

 

[arnelgp]

on the click event of the button:

Private Sub dmdOpenRptMonthlySales_Click()

    If Me.cboMonthYearStart.ListIndex < 0 Then
        MsgBox "Please select starting Year and Month from the combobox"
        Me.cboMonthYearStart.SetFocus
        Exit Sub
    End If
 
    If Me.cboMonthYearEnd.ListIndex < 0 Then
        MsgBox "Please select ending Year and Month from the combobox"
        Me.cboMonthYearEnd.SetFocus
        Exit Sub
    End If
 
    Dim dteStart As Date, dteEnd As Date
    Dim var, flter$
    ' create starting date
    var = Split(Me.cboMonthYearStart, " ")
    dteStart = DateSerial(var(0), var(1), 1)
    ' create ending date
    var = Split(Me.cboMonthYearEnd, " ")
    dteEnd = DateSerial(var(0), Val(var(1)) + 1, 0)
 
    ' create a filter
    flter = "[Date] Between #" & Format$(dteStart, "mm/dd/yyyy") & "# And #" & Format$(dteEnd, "mm/dd/yyyy") & "#"
 
    'close the report if already open
    On Error Resume Next
    DoCmd.Close acReport, "rptMonthlySales"
    On Error GoTo 0
 
    'open the report
    DoCmd.OpenReport ReportName:="rptMonthlySales", WhereCondition:=flter
 
End Sub

 

[Gasman]

My other reports have the code in the on open I think it is called event. I was hoping to have the code work in the button and not have the code in the report. Then I can have fewer reports. The reports would get filtered by the button, so I could use the same report.

Yes, I saw where you set a filter, but do not use it.
Look up the syntax of OpenReport. One of the parameters is to set a filter. Syntax is like sql, but without the SELECT.

 

[Ken Sheridan]

Then I have a button which is called cmdOpenRptMonthlySales. I would like to put the code in the button so that it filters the report between the year month selected from the combo boxes.

As the values in the controls are sortable you can change the existing code in the button's Click event procedure to use a Between...And operation in the OpenReport method's WhereCondition argument as below:

'------------------------------------------------------------
' cmdOpenRptOrderTotalsByMonth_Click
'
'------------------------------------------------------------
Private Sub cmdOpenRptOrderTotalsByMonth_Click()
On Error GoTo cmdOpenRptOrderTotalsByMonth_Click_Err

    Dim strCriteria As String
    strCriteria = "YearMonth Between """ & Me.cboMonthYearStart & """ And """ & Me.cboMonthYearEnd & """"
   
    DoCmd.OpenReport "rptOrderTotalsByMonth", View:=acViewReport, WhereCondition:=strCriteria

cmdOpenRptOrderTotalsByMonth_Click_Exit:
    Exit Sub

cmdOpenRptOrderTotalsByMonth_Click_Err:
    MsgBox Error$
    Resume cmdOpenRptOrderTotalsByMonth_Click_Exit

End Sub

 

[CraigDouglas]

As the values in the controls are sortable you can change the existing code in the button's Click event procedure to use a Between...And operation in the OpenReport method's WhereCondition argument as below:

'------------------------------------------------------------
' cmdOpenRptOrderTotalsByMonth_Click
'
'------------------------------------------------------------
Private Sub cmdOpenRptOrderTotalsByMonth_Click()
On Error GoTo cmdOpenRptOrderTotalsByMonth_Click_Err

    Dim strCriteria As String
    strCriteria = "YearMonth Between """ & Me.cboMonthYearStart & """ And """ & Me.cboMonthYearEnd & """"
  
    DoCmd.OpenReport "rptOrderTotalsByMonth", View:=acViewReport, WhereCondition:=strCriteria

cmdOpenRptOrderTotalsByMonth_Click_Exit:
    Exit Sub

cmdOpenRptOrderTotalsByMonth_Click_Err:
    MsgBox Error$
    Resume cmdOpenRptOrderTotalsByMonth_Click_Exit

End Sub

I got this code to work. Thank you so much. Craig

 

[Ken Sheridan]

Looking at your form again, shouldn't the referenced controls be as follows?

    strCriteria = "YearMonth Between """ & cboStartYearMonthOrderTotals & """ And """ & cboEndYearMonthOrderTotals & """"

PS: You really, really should include an Option Explicit line in the declarations area of each module.

 

[Ken Sheridan]

I've had another look at your form, and I think the following code would be better as it restricts the results by the OrderDate column in the base table rather than by a computed column. The latter should work, but I always prefer to go back to the original data where possible:

'------------------------------------------------------------
' cmdOpenRptOrderTotalsByMonth_Click
'
'------------------------------------------------------------
Private Sub cmdOpenRptOrderTotalsByMonth_Click()
On Error GoTo cmdOpenRptOrderTotalsByMonth_Click_Err

    Dim strCriteria As String
    Dim dtmStartDate As Date
    Dim dtmEndDate As Date
    
    dtmStartDate = CDate(cboStartYearMonthOrderTotals & " 01")
    dtmEndDate = DateAdd("m", 1, CDate(cboEndYearMonthOrderTotals & " 01"))
    
    strCriteria = "OrderDate >= #" & Format(dtmStartDate, "yyyy-mm-dd") & "# And OrderDate < #" & Format(dtmEndDate, "yyyy-mm-dd") & "#"
    
    DoCmd.OpenReport "rptOrderTotalsByMonth", View:=acViewReport, WhereCondition:=strCriteria

cmdOpenRptOrderTotalsByMonth_Click_Exit:
    Exit Sub

cmdOpenRptOrderTotalsByMonth_Click_Err:
    MsgBox Error$
    Resume cmdOpenRptOrderTotalsByMonth_Click_Exit

End Sub