Filter By Form using LIKE clause

[SirPoonga]

Is it possible that when having a user using Filter By Form in a form
that no matter what the user enters in for a value that "LIKE *Value*"
gets automatically replaced? Would I have to do something in the On
Filter or On Apply Filter event?

For example, say I have a form displaying part numbers in datasheet
view. A part number, for example, will look like 123-4567-89-12-1
where each part of the part number means something. Lets say I want to
filter out all the part numbers that contain "4567". In SQL I'd make a
query where the WHERE clasue would contain "partnumber LIKE *4567*".
But if I entered that in witht eh default setup for filter by for it
does something like "partnumber = 4567".

Is there a way to get filter by form to act like that or do I just have
to make my own custom form to handle something like this?

 

[SirPoonga]

On a related note, can I sort the drop list for each field in filter by
form?

 

[Allen Browne]

No. You have to type the LIKE operator into the Filter By Form (FBF) screen
if you want it to use it.

You probably are better off creating your own interface for filtering. There
are usually only a few fields on any form that the user really needs to
search/filter on, so what we prefer to do is add some unbound filter boxes
to the form itself. We use the Form Header section and color them
differently so the interface is obvious. Then add 2 command buttons for
Filter and Remove Filter. Click of the Filter button builds a Where string
based on the boxes where the user entered something, and applies it as the
Filter of the form. Because you are coding this yourself, you can use
whatever operator suits, and set it up for a range of values (e.g. dates)
where appropriate.

This also avoids the other major limitations of FBF: it does not work in the
runtime environment, you lose all code/events while in FBF, and you don't
end up with nonsense filters based on lookup fields which you have to handle
as special cases if you open a report filtered the same way as the form.

 

[Allen Browne]

No. FBF can't do that either.

 

[SirPoonga]

I ended up doing this


Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
Me.Filter = Replace(Me.Filter, "=""", " LIKE ""*")
Me.Filter = Replace(Me.Filter, """)", "*"")")
'MsgBox Me.Filter
End Sub
Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
Me.Filter = Replace(Me.Filter, " LIKE ""*", "=""")
Me.Filter = Replace(Me.Filter, "*"")", """)")
End Sub

 

[Tom Ross]

and furthermore the 'unbound filter boxes' Allen refers to to make your
filters can be Combo Boxes where the Sort Order of the items in the dropdown
menu an be controlled. by the recordsource query of the combo box (referring
to your other question)
Tom

 

[Bob Quintal]

Is it possible that when having a user using Filter By Form in
a form that no matter what the user enters in for a value that
"LIKE *Value*" gets automatically replaced? Would I have to
do something in the On Filter or On Apply Filter event?

For example, say I have a form displaying part numbers in
datasheet view. A part number, for example, will look like
123-4567-89-12-1 where each part of the part number means
something. Lets say I want to filter out all the part numbers
that contain "4567". In SQL I'd make a query where the WHERE
clasue would contain "partnumber LIKE *4567*". But if I
entered that in witht eh default setup for filter by for it
does something like "partnumber = 4567".

Is there a way to get filter by form to act like that or do I
just have to make my own custom form to handle something like
this?

Just enter *4567* in the control for the partnumber field. It
works fine.