Filtering for dates

M

Maver1ck666

I have a form with a number of combo boxes in the header to act as a filter
for each column.

Each combo box has been assigned the appropriate tag and has the following
code on after update:

DoCmd.ApplyFilter , "" & Me.Filter1.Tag & " = " & Me.Filter1 & ""

My problem is this. The filters work fine on anything that is not a date!
The date fields are formated as short date and I have done the same with the
combo boxes but still no joy.

Does anyone have any suggestions please as its the date I need to filter the
most :(

Cheers!
 
M

Maurice

Remember that date fields should be addressed with pund signs:

"#" & Me.Filter1 & "#""

do you use the tag of the control to store the chosen values? In that case
you could also write the pound signs there...

Maurice
 
A

Allen Browne

To build the filter string, the literal value needs to be enclosed in # for
date fields, quote marks for Text fields, and no delimiter for numeric
fields.

You therefore need 3 pieces of information to build the filter string:
a) the field name to filter on;
b) the field type, to determine the delimiter;
c) the value to filter for; and possibly
d) the caption the user knows this field by.
Perhaps you could do this with a 3-column combo?

An alternative (somewhat less desirable) alternative is to use the Like
operator, which treats the value as text regardless of its type, so you
would use:
DoCmd.ApplyFilter , Me.Filter1.Tag & " Like """ & Me.Filter1 & """"

If you want the user to be able to enter criteria in several of those
combos, and you build the filter from all the ones where the user entered
something, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Another possible approach is to offer a combo where the user chooses which
field to filter on, and a text box where they enter the value to match. You
can set this one up just by copying and pasting, without having to change
any code:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
 
Top