Filter by Date Not Working

[PaultheS]

My form currently has three text boxes that a user can put filter criteria in, and then an "Apply Filter" button that applies the filter to the form (big surprise). The button has code behind it to build the filter statement.

Two of the boxes work fine, which are E-mail Address (string) and Member Number (integer). The problem is occuring with the Date filter. The text box is set to 'Short Date', and the field in the table is set to 'Date/Time'.

I can't seem to get the filter to work properly. "[Date] = '" & DateBox.Value & "'" won't work, "[Date] = " & DateBox.Value won't work (for example)... I keep trying variations and I either get no results or an error saying 'You cancelled the previous operation'. Any guidance in how I should be building this filter?

Er...

While I was typing this post, I actually found a way to make it work, but I came this far so I might as well post it in case anyone has the same problem. Alternatively, if anyone has a nicer solution, or if there's some reason why I shouldn't use this one, let me know...

The problem was (I believe) that it was comparing a Date type to something that wasn't a date type. I changed my filter to:

Form.Filter = "[Date] = CDate(" & DateBox.Value & ")"

and that fixed it.

 

[PHV]

[Date] = #" & DateBox.Value & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886