Combobox filter by month (1 Viewer)

ivonsurf123

Registered User.
Local time
Today, 12:45
Joined
Dec 8, 2017
Messages
69
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.

Code:
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
 

ivonsurf123

Registered User.
Local time
Today, 12:45
Joined
Dec 8, 2017
Messages
69
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

Banishment Pending
Local time
Today, 14:45
Joined
May 11, 2011
Messages
11,611
What value does cboMonth hold? Is it a name or a number?
 

plog

Banishment Pending
Local time
Today, 14:45
Joined
May 11, 2011
Messages
11,611
Can you post your code for the filter using the Month() function?
 

ivonsurf123

Registered User.
Local time
Today, 12:45
Joined
Dec 8, 2017
Messages
69
This is how is set:

Code:
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

Banishment Pending
Local time
Today, 14:45
Joined
May 11, 2011
Messages
11,611
No where in that code do you use the Month() function.
 

ivonsurf123

Registered User.
Local time
Today, 12:45
Joined
Dec 8, 2017
Messages
69
I have use this ones, but they did not filter:

Code:
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

Code:
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

Banishment Pending
Local time
Today, 14:45
Joined
May 11, 2011
Messages
11,611
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

Registered User.
Local time
Today, 12:45
Joined
Dec 8, 2017
Messages
69
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

Banishment Pending
Local time
Today, 14:45
Joined
May 11, 2011
Messages
11,611
Your initial post wanted to filter without regard for year:

Code:
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

Registered User.
Local time
Today, 12:45
Joined
Dec 8, 2017
Messages
69
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

Banishment Pending
Local time
Today, 14:45
Joined
May 11, 2011
Messages
11,611
Please post sample data to demonstrate what you are trying to accomplish.
 

ivonsurf123

Registered User.
Local time
Today, 12:45
Joined
Dec 8, 2017
Messages
69
Thank you plog, attached is a sample of the DB
 

Attachments

  • InvoiceTracking.accdb
    704 KB · Views: 73

JHB

Have been here a while
Local time
Today, 20:45
Joined
Jun 17, 2012
Messages
7,732
What do you mean by "month by month"?

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

 

Attachments

  • Dates.jpg
    Dates.jpg
    11.2 KB · Views: 216

Users who are viewing this thread

Top Bottom