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

[NeoPa]

You can use either the Month() function or the Format() function with an "m" parameter.

[ivonsurf123]

I tried that way, but did not filter at all

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

[NeoPa]

I didn't give you a solution. I gave you an approach.

Just so we're clear, your code uses a completely different approach than either of those I suggested. Your approach is the sort of cumbersome, clumsy (and ultimately unworkable) approach I was trying to help you to avoid.

If you are unclear about how to implement a solution using either of the approaches I suggested then I'm sure we can work something out when I know what you can understand.

[ivonsurf123]

Thank you for your approach NeoPa, I tried it as well, but it did not even filter, the first code I submitted at least filter all the months of the current year, just need to figure out how to do it for past years, by the way these are two of the many codes I tried with the Month() function or the Format() function with an "m" parameter.

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

[twinnyfo]

Ivon,

Why did that second block not work? It look like it should be fine, except that I would remove the single quotes from Line 8.

Hope this hepps!

[NeoPa]

Hi IvonSurf.

Thank you for responding with the relevant code (IE the code you used to try to implement my earlier suggestions). Now it's easy to see what it is you've done wrong and direct you towards something that can work.

First of all, I should point out that the idea of using the Format() function was to ensure you were dealing with only the month part of the date. Once you start including the year part then it can't possibly work (As you're trying to compare the month regardless of the year).

The next step is to decide whether you prefer to use the two- or three- character codes for the month, or even the numerical representation. Whichever it is you decide on, you must use the same for both sides of the comparison. If you choose the numeric version (which incidentally is what the Month() function returns) then you won't be using quotes for your literal value (See Quotes (') and Double-Quotes (") - Where and When to use them). Be especially careful here too as Format(), even Format(,"mm"), returns a string value. There is a very big difference between "10" and 10. Confuse these at your peril.

For now I'll use the three-character month codes as an example (This means we can't use Month()).

Private Sub Form_Load()
    Me.cboMonth.RowSource = "Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec"
End Sub

NB. I only included this part to illustrate that you don't need it at all. You set this up in the design of the ComboBox and it just works.

Private Sub cboMonth_AfterUpdate()
    Dim strFilter As String
On Error GoTo Proc_Error

    With Me
        With .cboMonth
            If Not IsNull(.Value) Then _
                strFilter = Replace("Format([InvoiceDate],'mmm')='%M'" _
                                  , "%M", .Value)
        End With
        With .subfrm_Invoice_Tracking.Form
            .Filter = strFilter
            .FilterOn = (strFilter > "")
        End With
    End With
    Exit Sub

Proc_Error:
    MsgBox "Error " & Err.Number & " in setting subform filter:" & vbCrLf & Err.Description
End Sub

NB. Code indenting is very important, but doing it wrong is even worse than not doing it at all. Indent evenly when you have groups of lines all at a the same level. Groups in loop, conditional and With statements should always result in indentation, as should line continuations of course.