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.
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.
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.
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
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.
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:
Code:
'------------------------------------------------------------
' 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
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:
Code:
'------------------------------------------------------------
' 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'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:
Code:
'------------------------------------------------------------
' 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
Could you also help me if I want to filter by 3 criteria? So the first two filters would be the same as before, the combo boxes will be cboYearMonthStart and cboYearMonthEnd, the field in the report is YearMonth, then filter by category, so the combo box would be cboCategory and the field in the report would be ProductCategory. The report I want to filter is rptMonthlySales. If the code could be in the button like last time. The on-click event of the button is called cmdOpenRptMonthlySales. Craig
Could you also help me if I want to filter by 3 criteria? So the first two filters would be the same as before, the combo boxes will be cboYearMonthStart and cboYearMonthEnd, the field in the report is YearMonth, then filter by category, so the combo box would be cboCategory and the field in the report would be ProductCategory. The report I want to filter is rptMonthlySales. If the code could be in the button like last time. The on-click event of the button is called cmdOpenRptMonthlySales. Craig
I've attached a zipped copy of your file with three combo boxes for the above added. The button's code is:
Code:
Private Sub cmdOpenRptMonthlySales_Click()
'------------------------------------------------------------
' cmdOpenRptMonthlySales_Click
'
'------------------------------------------------------------
On Error GoTo cmdOpenRptMonthlySales_Click_Err
Dim strCriteria As String
Dim dtmStartDate As Date
Dim dtmEndDate As Date
dtmStartDate = CDate(cboYearMonthStart & " 01")
dtmEndDate = DateAdd("m", 1, CDate(cboYearMonthEnd & " 01"))
strCriteria = "OrderDate >= #" & Format(dtmStartDate, "yyyy-mm-dd") & "# And OrderDate < #" & Format(dtmEndDate, "yyyy-mm-dd") & "#"
If Not IsNull(Me.cboCategory) Then
strCriteria = strCriteria & " And ProductCategory = """ & Me.cboCategory & """"
End If
DoCmd.OpenReport "rptOrderTotalsByMonth", View:=acViewReport, WhereCondition:=strCriteria
cmdOpenRptMonthlySales_Click_Exit:
Exit Sub
cmdOpenRptMonthlySales_Click_Err:
MsgBox Error$
Resume cmdOpenRptMonthlySales_Click_Exit
End Sub
By examining the cboCategory combo box for Null it is made optional, so you can select start and end year/months and leave the category combo box empty if you wish the report will then include all categories. If you select a category it will be filtered to the data within the data range for that category only.
I've had a look at the other controls in the form and included code for them also in the respective buttons' Click event procedures. This mostly uses the same methodology to restrict the report to a data range. The exception is the cmdOpenRptOrderByMonthOrYearOrCombo button, whose code is:
Code:
'------------------------------------------------------------
' cmdOpenRptOrderByMonthOrYearOrCombo_Click
'
'------------------------------------------------------------
Private Sub cmdOpenRptOrderByMonthOrYearOrCombo_Click()
On Error GoTo cmdOpenRptOrderByMonthOrYearOrCombo_Click_Err
Dim strCriteria As String
Dim dtmStartDate As Date
Dim dtmEndDate As Date
If Not IsNull(Me.cboMonth) Then
dtmStartDate = DateSerial(Me.cboYear, Me.cboMonth, 1)
dtmEndDate = DateSerial(Me.cboYear, Me.cboMonth + 1, 1)
Else
dtmStartDate = DateSerial(Me.cboYear, 1, 1)
dtmEndDate = DateSerial(Me.cboYear + 1, 1, 1)
End If
strCriteria = "OrderDate >= #" & Format(dtmStartDate, "yyyy-mm-dd") & "# And OrderDate < #" & Format(dtmEndDate, "yyyy-mm-dd") & "#"
DoCmd.OpenReport "rptOrderByMonthOrYearOrCombo", View:=acViewReport
cmdOpenRptOrderByMonthOrYearOrCombo_Click_Exit:
Exit Sub
cmdOpenRptOrderByMonthOrYearOrCombo_Click_Err:
MsgBox Error$
Resume cmdOpenRptOrderByMonthOrYearOrCombo_Click_Exit
End Sub
This allows you to select a year and month, or a year only, so the report will be filtered to one month's data or a whole year's data.
One thing to note about the code is that a date range is defined not by means of a BETWEEN...AND operation, but as on or later than the start date and before the day following the end date. The former might omit data if an order date on the final day of the range includes a non-zero time of day element. You cannot exclude this possibility with complete confidence unless specific provision has been made in the table definition to disallow such date/time values, i.e. a Validation Rule which allows only integer values in the OrderDate column.
I've attached a zipped copy of your file with three combo boxes for the above added. The button's code is:
Code:
Private Sub cmdOpenRptMonthlySales_Click()
'------------------------------------------------------------
' cmdOpenRptMonthlySales_Click
'
'------------------------------------------------------------
On Error GoTo cmdOpenRptMonthlySales_Click_Err
Dim strCriteria As String
Dim dtmStartDate As Date
Dim dtmEndDate As Date
dtmStartDate = CDate(cboYearMonthStart & " 01")
dtmEndDate = DateAdd("m", 1, CDate(cboYearMonthEnd & " 01"))
strCriteria = "OrderDate >= #" & Format(dtmStartDate, "yyyy-mm-dd") & "# And OrderDate < #" & Format(dtmEndDate, "yyyy-mm-dd") & "#"
If Not IsNull(Me.cboCategory) Then
strCriteria = strCriteria & " And ProductCategory = """ & Me.cboCategory & """"
End If
DoCmd.OpenReport "rptOrderTotalsByMonth", View:=acViewReport, WhereCondition:=strCriteria
cmdOpenRptMonthlySales_Click_Exit:
Exit Sub
cmdOpenRptMonthlySales_Click_Err:
MsgBox Error$
Resume cmdOpenRptMonthlySales_Click_Exit
End Sub
By examining the cboCategory combo box for Null it is made optional, so you can select start and end year/months and leave the category combo box empty if you wish the report will then include all categories. If you select a category it will be filtered to the data within the data range for that category only.
I've had a look at the other controls in the form and included code for them also in the respective buttons' Click event procedures. This mostly uses the same methodology to restrict the report to a data range. The exception is the cmdOpenRptOrderByMonthOrYearOrCombo button, whose code is:
Code:
'------------------------------------------------------------
' cmdOpenRptOrderByMonthOrYearOrCombo_Click
'
'------------------------------------------------------------
Private Sub cmdOpenRptOrderByMonthOrYearOrCombo_Click()
On Error GoTo cmdOpenRptOrderByMonthOrYearOrCombo_Click_Err
Dim strCriteria As String
Dim dtmStartDate As Date
Dim dtmEndDate As Date
If Not IsNull(Me.cboMonth) Then
dtmStartDate = DateSerial(Me.cboYear, Me.cboMonth, 1)
dtmEndDate = DateSerial(Me.cboYear, Me.cboMonth + 1, 1)
Else
dtmStartDate = DateSerial(Me.cboYear, 1, 1)
dtmEndDate = DateSerial(Me.cboYear + 1, 1, 1)
End If
strCriteria = "OrderDate >= #" & Format(dtmStartDate, "yyyy-mm-dd") & "# And OrderDate < #" & Format(dtmEndDate, "yyyy-mm-dd") & "#"
DoCmd.OpenReport "rptOrderByMonthOrYearOrCombo", View:=acViewReport
cmdOpenRptOrderByMonthOrYearOrCombo_Click_Exit:
Exit Sub
cmdOpenRptOrderByMonthOrYearOrCombo_Click_Err:
MsgBox Error$
Resume cmdOpenRptOrderByMonthOrYearOrCombo_Click_Exit
End Sub
This allows you to select a year and month, or a year only, so the report will be filtered to one month's data or a whole year's data.
One thing to note about the code is that a date range is defined not by means of a BETWEEN...AND operation, but as on or later than the start date and before the day following the end date. The former might omit data if an order date on the final day of the range includes a non-zero time of day element. You cannot exclude this possibility with complete confidence unless specific provision has been made in the table definition to disallow such date/time values, i.e. a Validation Rule which allows only integer values in the OrderDate column.
