Combobox filter by month

[ivonsurf123]

Hello,

Could you help me to figure out how can I filter by month not only for the current year but for past years as well? I have tried but without a solution. Thank you.

Private Sub cboMonth_AfterUpdate()
Dim dates(1) As Date
    
    If Me.cboMonth = 0 Then
       Me.subfrm_Invoice_Tracking.Form.Filter = ""
       Me.subfrm_Invoice_Tracking.Form.FilterOn = False
    Else
        dates(0) = DateSerial(Year(Date), Me.cboMonth, 1)
        dates(1) = DateSerial(Year(Date), Me.cboMonth + 1, 1) - 1
    
        Me.subfrm_Invoice_Tracking.Form.Filter = _
            "InvoiceDate >= #" & dates(0) & "# " & _
            "AND InvoiceDate <= #" & dates(1) & "#"
        Me.subfrm_Invoice_Tracking.Form.FilterOn = True
    End If
    
End Sub

 

[plog]

Use the Month function (https://www.techonthenet.com/access/functions/date/month.php).

Month([InvoiceDate])=Me.cboMonth

 

[ivonsurf123]

Thank you plog, but I have done it with Month([InvoiceDate])=Me.cboMonth, it did not work not even filtering the month nor the years when click in months.

 

[plog]

What value does cboMonth hold? Is it a name or a number?

 

[ivonsurf123]

It's a number.

 

[plog]

Can you post your code for the filter using the Month() function?

 

[ivonsurf123]

This is how is set:

Private Sub SetUpMonthFilterCombo()
    Dim tmp As String
    Dim i As Integer
    
    With Me.cboMonth
        tmp = "0; < Clear >"
        For i = 1 To 12
            tmp = tmp & ";" & i & ";" & MonthName(i)
        Next
        .ColumnCount = 2
        .BoundColumn = 1
        .ColumnWidths = "0;2"
        .RowSourceType = "Value List"
        .RowSource = tmp
        .AfterUpdate = "[Event Procedure]"
    End With


End Sub


Private Sub Form_Load()

SetUpMonthFilterCombo



Private Sub cboMonth_AfterUpdate()
Dim dates(1) As Date
    
    If Me.cboMonth = 0 Then
       Me.subfrm_Invoice_Tracking.Form.Filter = ""
       Me.subfrm_Invoice_Tracking.Form.FilterOn = False
    Else
        dates(0) = DateSerial(Year(Date), Me.cboMonth, 1)
        dates(1) = DateSerial(Year(Date), Me.cboMonth + 1, 1) - 1
        Me.subfrm_Invoice_Tracking.Form.Filter = _
            "InvoiceDate >= #" & dates(0) & "# " & _
            "AND InvoiceDate <= #" & dates(1) & "#"
        Me.subfrm_Invoice_Tracking.Form.FilterOn = True
    End If
   
End Sub

 

[plog]

No where in that code do you use the Month() function.

 

[ivonsurf123]

I have use this ones, but they did not filter:

Private Sub Form_Load()
 Dim strItems As String
 Dim intI As Integer

For intI = 0 To 150
    strItems = strItems & Format(DateAdd("m", intI, #1/2/2016#), "mmm-yyyy") & ";"
Next intI
    '/ Remove end bits
    strItems = Left(strItems, Len(strItems) - 1)
    '/ Populate combo.listbox
    Me.cboMonth.RowSource = strItems

End Sub

Private Sub cboMonth_AfterUpdate()
On Error GoTo Proc_Error
       
If IsNull(Me.cboMonth) Then
   Me.subfrm_Invoice_Tracking.Form.Filter = ""
   Me.subfrm_Invoice_Tracking.Form.FilterOn = False
Else
  Me.subfrm_Invoice_Tracking.Form.Filter = "Month(InvoiceDate)='" & Me.cboMonth & "'
  Me.subfrm_Invoice_Tracking.Form.FilterOn = True
End If
    
Proc_Exit:
   Exit Sub
Proc_Error:
   MsgBox "Error " & Err.Number & " in setting subform filter:" & vbCrLf & Err.Description
   Resume Proc_Exit
   
End Sub

 

[plog]

You are missing a double quote at the end of this line:

Me.subfrm_Invoice_Tracking.Form.Filter = "Month(InvoiceDate)='" & Me.cboMonth & "'

However, you shouldn't need to escape taht at all. This shoudl work:

Me.subfrm_Invoice_Tracking.Form.Filter = "Month(InvoiceDate)=" & Me.cboMonth

 

[ivonsurf123]

Thank you, but how would you that code to filter by month but with year as well, per example if you have Nov, 2017 records and Nov, 2018 records how can you filter them?

Me.subfrm_Invoice_Tracking.Form.Filter = "Month(InvoiceDate)=" & Me.cboMonth

 

[plog]

Your initial post wanted to filter without regard for year:

Could you help me to figure out how can I filter by month not only for the current year but for past years as well?

I'm lost as to what you want to accomplish. Perhaps you can demonstrate it with data.
Post a bunch of dates and then show me which ones should show on the form.

 

[ivonsurf123]

Thank you. This was my original request for help: "Could you help me to figure out how can I filter by month not only for the current year but for past years as well? I have tried but without a solution."

the original post carried a code which only filter the current year (2018) month by month, but I am unable to make work for past years( 2016, 2017) month by month

 

[plog]

Please post sample data to demonstrate what you are trying to accomplish.

 

[ivonsurf123]

Thank you plog, attached is a sample of the DB

 

[JHB]

What do you mean by "month by month"?

What result do you want if you choose April?
And the same for May and November?