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.