Filtering for dates

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

Guest

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!
 
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
 
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
 
Back
Top