423,323 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,323 IT Pros & Developers. It's quick & easy.

Combobox filter by month

P: 27
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboMonth_AfterUpdate()
  2. Dim dates(1) As Date
  3.  
  4.     If Me.cboMonth = 0 Then
  5.        Me.subfrm_Invoice_Tracking.Form.Filter = ""
  6.        Me.subfrm_Invoice_Tracking.Form.FilterOn = False
  7.     Else
  8.         dates(0) = DateSerial(Year(Date), Me.cboMonth, 1)
  9.         dates(1) = DateSerial(Year(Date), Me.cboMonth + 1, 1) - 1
  10.  
  11.         Me.subfrm_Invoice_Tracking.Form.Filter = _
  12.             "InvoiceDate >= #" & dates(0) & "# " & _
  13.             "AND InvoiceDate <= #" & dates(1) & "#"
  14.         Me.subfrm_Invoice_Tracking.Form.FilterOn = True
  15.     End If
  16.  
  17. End Sub
  18.  
May 15 '18 #1

✓ answered by 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!

Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 30,996
You can use either the Month() function or the Format() function with an "m" parameter.
May 15 '18 #2

P: 27
I tried that way, but did not filter at all

this is how is set:

Expand|Select|Wrap|Line Numbers
  1. Private Sub SetUpMonthFilterCombo()
  2.     Dim tmp As String
  3.     Dim i As Integer
  4.  
  5.     With Me.cboMonth
  6.         tmp = "0; < Clear >"
  7.         For i = 1 To 12
  8.             tmp = tmp & ";" & i & ";" & MonthName(i)
  9.         Next
  10.         .ColumnCount = 2
  11.         .BoundColumn = 1
  12.         .ColumnWidths = "0;2"
  13.         .RowSourceType = "Value List"
  14.         .RowSource = tmp
  15.         .AfterUpdate = "[Event Procedure]"
  16.     End With
  17.  
  18.  
  19. End Sub
  20.  
  21.  
  22. Private Sub Form_Load()
  23.  
  24. SetUpMonthFilterCombo
  25.  
  26.  
  27.  
  28. Private Sub cboMonth_AfterUpdate()
  29. Dim dates(1) As Date
  30.  
  31.     If Me.cboMonth = 0 Then
  32.        Me.subfrm_Invoice_Tracking.Form.Filter = ""
  33.        Me.subfrm_Invoice_Tracking.Form.FilterOn = False
  34.     Else
  35.         dates(0) = DateSerial(Year(Date), Me.cboMonth, 1)
  36.         dates(1) = DateSerial(Year(Date), Me.cboMonth + 1, 1) - 1
  37.         Me.subfrm_Invoice_Tracking.Form.Filter = _
  38.             "InvoiceDate >= #" & dates(0) & "# " & _
  39.             "AND InvoiceDate <= #" & dates(1) & "#"
  40.         Me.subfrm_Invoice_Tracking.Form.FilterOn = True
  41.     End If
  42.  
  43. End Sub
  44.  
May 16 '18 #3

NeoPa
Expert Mod 15k+
P: 30,996
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.
May 16 '18 #4

P: 27
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  Dim strItems As String
  3.  Dim intI As Integer
  4.  
  5. For intI = 0 To 150
  6.     strItems = strItems & Format(DateAdd("m", intI, #1/2/2016#), "mmm-yyyy") & ";"
  7. Next intI
  8.     '/ Remove end bits
  9.     strItems = Left(strItems, Len(strItems) - 1)
  10.     '/ Populate combo.listbox
  11.     Me.cboMonth.RowSource = strItems
  12.  
  13. End Sub
  14.  
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboMonth_AfterUpdate()
  2. On Error GoTo Proc_Error
  3.  
  4. If IsNull(Me.cboMonth) Then
  5.    Me.subfrm_Invoice_Tracking.Form.Filter = ""
  6.    Me.subfrm_Invoice_Tracking.Form.FilterOn = False
  7. Else
  8.   Me.subfrm_Invoice_Tracking.Form.Filter = "Month(InvoiceDate)='" & Me.cboMonth & "'
  9.   Me.subfrm_Invoice_Tracking.Form.FilterOn = True
  10. End If
  11.  
  12. Proc_Exit:
  13.    Exit Sub
  14. Proc_Error:
  15.    MsgBox "Error " & Err.Number & " in setting subform filter:" & vbCrLf & Err.Description
  16.    Resume Proc_Exit
  17.  
  18. End Sub
  19.  
May 17 '18 #5

twinnyfo
Expert Mod 2.5K+
P: 2,536
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!
May 17 '18 #6

NeoPa
Expert Mod 15k+
P: 30,996
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()).
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Me.cboMonth.RowSource = "Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec"
  3. 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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboMonth_AfterUpdate()
  2.     Dim strFilter As String
  3. On Error GoTo Proc_Error
  4.  
  5.     With Me
  6.         With .cboMonth
  7.             If Not IsNull(.Value) Then _
  8.                 strFilter = Replace("Format([InvoiceDate],'mmm')='%M'" _
  9.                                   , "%M", .Value)
  10.         End With
  11.         With .subfrm_Invoice_Tracking.Form
  12.             .Filter = strFilter
  13.             .FilterOn = (strFilter > "")
  14.         End With
  15.     End With
  16.     Exit Sub
  17.  
  18. Proc_Error:
  19.     MsgBox "Error " & Err.Number & " in setting subform filter:" & vbCrLf & Err.Description
  20. 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.
May 17 '18 #7

Post your reply

Sign in to post your reply or Sign up for a free account.