By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,414 Members | 1,057 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,414 IT Pros & Developers. It's quick & easy.

FIltering on a form using just the year from a date field

P: 2
I have created two unbound boxes on a form that users can enter name [cbonamefilter] and date [cmboyearfilter]. I want the form to filter by name and the "YYYY" part of the date. I have researched the forum and found most relative responses and tried to use suggested coding. The name portion of the filter works great but I'm having trouble getting the year part to work. I was successful when I was entered an exact date but not for just the year portion. I'm new to VBA so I'm not sure what I am missing. I get a runtime error when I try to run the code below.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cbonamefilter_AfterUpdate()
  2. Call CheckFilter
  3. End Sub
  4. Private Sub cmboyearfilter_AfterUpdate()
  5. Me!cmboyearfilter = IIf(IsDate(Me!cmboyearfilter), _
  6.                                   Format(Me!cmboyearfilter, "mm/dd/yyyy"), _ "")
  7.  
  8. Call CheckFilter
  9. End Sub
  10.  
  11. Private Sub CheckFilter()
  12.  
  13. Dim strFilter As String, strOldFilter As String
  14. strOldFilter = Me.Filter
  15.  
  16. 'cbonamefilter - text
  17. If Me!cbonamefilter > "" Then _
  18. strFilter = strFilter & _
  19. " AND ([Initials-Pre] Like '" & _
  20. Me!cbonamefilter & "*')"
  21.  
  22. 'cmboyearfilter - date
  23. If Me!cmboyearfilter > "" Then _
  24. strFilter = strFilter & _
  25. " AND ([PreCal Date] BETWEEN #1/1/yyyy# AND #12/31/yyyy#=" & _
  26.                      Format(CDate(Me!cmboyearfilter), _
  27.                             "\#mm/dd/yyyy\#") & ")"
  28.  
  29.  
  30.  
  31. If strFilter > "" Then strFilter = Mid(strFilter, 6)
  32.      If strFilter <> strOldFilter Then
  33.          Me.Filter = strFilter
  34.          Me.FilterOn = (strFilter > "")
  35.      End If
  36.  End Sub
  37.  
Thanks for any help
Jul 2 '15 #1
Share this Question
Share on Google+
4 Replies


jforbes
Expert 100+
P: 1,107
I think this is what you are looking for:
Expand|Select|Wrap|Line Numbers
  1. strFilter = strFilter & _
  2.  " AND (YEAR([PreCal Date])=" &  Me!cmboyearfilter & ")"
Jul 2 '15 #2

P: 2
Well that stopped the runtime error but no records are returned no matter what date is entered. Maybe I have to do something to make the [cmboyearfilter] formatted to a year as well?

I used the same logic as above and I think I got it. Thanks.
Jul 2 '15 #3

jforbes
Expert 100+
P: 1,107
I have to admit that I didn't read through all of the code. I just saw the point where the SQL was being created and so I assumed the that Me!cmboyearfilter would be just an integer to represent the Year. Glad it all worked out for you.
Jul 3 '15 #4

zmbd
Expert Mod 5K+
P: 5,397
perhaps this will guide you, slightly different application with the grouping instead of with a cbobx; however, note how the SQL is written to return month and year from the data...
http://bytes.com/topic/access/answer...th#post3726215
you should be able to modify the concept to work as the recordsource for your cbobx
Jul 12 '15 #5

Post your reply

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