Filtering by month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I used this help below from a past post by Allen Browne and was easily able to filter by a text field called author. However, I also want to filter by month and/or year. With the code below my filter works only if I type in the exact day/month/year. Any help on this would be appreciated.

Helf from a former post:

Try this in the After Update event procedure of your text box (named "txt1"
in this example):

Private Sub txt1_AfterUpdate()
If IsNull(Me.[txt1]) Then
Me.FilterOn = False
Else
Me.Filter = "[SomeField] = " & Me.txt1
Me.FilterOn = True
End If
End Sub

If SomeField is a Text type field (not Number), you need extra quotes:
Me.Filter = "[SomeField] = """ & Me.txt1 & """"
If it is a Date fiels use the # as delimiter:
Me.Filter = "[SomeField] = #" & Me.txt1 & "#"
 
Me.Filter = "Format([SomeDateField, 'yyyymm') = """ & Format(Date, "yyyymm")
& """"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Karnegie23 said:
I used this help below from a past post by Allen Browne and was easily
Click to expand...
able to filter by a text field called author. However, I also want to filter
by month and/or year. With the code below my filter works only if I type in
the exact day/month/year. Any help on this would be appreciated.
Helf from a former post:

Try this in the After Update event procedure of your text box (named "txt1"
in this example):

Private Sub txt1_AfterUpdate()
If IsNull(Me.[txt1]) Then
Me.FilterOn = False
Else
Me.Filter = "[SomeField] = " & Me.txt1
Me.FilterOn = True
End If
End Sub

If SomeField is a Text type field (not Number), you need extra quotes:
Me.Filter = "[SomeField] = """ & Me.txt1 & """"
If it is a Date fiels use the # as delimiter:
Me.Filter = "[SomeField] = #" & Me.txt1 & "#"
Click to expand...
 
Back
Top