Filtering partial dates (by selection) in a form

[beesee]

With help from forum members I have been successful in setting up filters in my form. Things have worked fine with text, but I cannot get this date control to filter. What am I doing wrong?

Private Sub Date_KeyDown(KeyCode As Integer, Shift As Integer)

Dim strDateFilter As String

'114 = F3 key
If KeyCode = 114 Then
   strDateFilter = Me.ActiveControl.SelText
   Me.Filter = "[Date] Like " & "#" & strDateFilter & "#"
   Me.FilterOn = True
   End If

End Sub

[pbaldy]

For that to work the text would have to be selected. Is it? Why not just use the .Value property?

[beesee]

Thanks Paul - yes the text or partial text is selected before the F3 is pressed. Say I have a date in the form field [Feb 17,2016], I would like to select say 2016, and then pressing F3 would filter all the dates with 2016 in them. Or I could select Feb, or Feb 17.

[pbaldy]

Oh, I think it's going to be more complicated then. I'd only expect that to work if you selected an entire valid date. To do what you're talking about would require a fair amount of logic to analyze what was selected and apply the correct filter. I'm assuming the [Date] field has the date/time data type?

By the way, "Date" is not a good field name. Access can (and will) confuse it with the Date() function.

[beesee]

Yes it works when selecting the entire date. When I use a similar function in text only fields, it the filtering works by selecting single words, and even letters in addition to the entire field.

Thanks for the tip on "Date". My Database was created years ago and has a bunch of linked tables, queries, forms etc. Would it be difficult to change the name? Is there any way of doing it that will automatically update all forms and queries, etc?

[pbaldy]

You can use wildcards with text, not really with date/time values. Internally, the date/time data type is stored as a double, where the integer portion represents the date, the decimal portion the time. This instant is 42419.3600694444:

?now
2/19/2016 8:38:27 AM
?cdbl(now())
42419.3600694444

So you can see that trying to search as you are wouldn't work. I would probably have a combo box with months and textboxes for day and year. Let the user fill out whichever they want and build a filter based on that.

[pbaldy]

Oh, and regarding Date, I would follow the "if it ain't broke don't fix it" philosophy and leave it alone. Just be aware if you get weird errors that Access can confuse the function and the field and you may need to clarify. I made the same mistake in my first "real" database, and it's still in 24/7 use 15+ years later.

[beesee]

Thanks again Paul. I will work with your idea with date combo boxes.

Brian

[pbaldy]

No problem Brian, post back if you get stuck.