Generic Search Capability Using Filters

[egun]

I am trying to add a "generic" search function to the main form of my
database that will allow the user to choose a field to search and enter a
string to search for. To do this, I have added two controls. The first is a
combo box whose source is the Field List from the table feeding the form.
The second is a text box in which the user can type a string to be searched
for.

Here is how I want it to work:
1. User selects the specific field to search in the combo box. This part
works fine.
2. User types in a text string in the text box (something like *ability or
%time or whatever). Also works fine.
3. User presses the "Search" button, and the database is filtered to show
only records for which the selected field matches the criterion.

I have tried using:

Me.Filter = "[" & SelectedFieldName & "] Like '" & SearchText & "' "
Me.FilterOn = True

but I always get zero records, even when the selected field is a text field.

I know this is probably harder than I want it to be. Any suggestions will
be greatly appreciated. Including "Trust me, it's too hard - just give up"


Eric

 

[Stefan Hoffmann]

hi,

Me.Filter = "[" & SelectedFieldName & "] Like '" & SearchText & "' "
Me.FilterOn = True

but I always get zero records, even when the selected field is a text field.

I know this is probably harder than I want it to be. Any suggestions will
be greatly appreciated. Including "Trust me, it's too hard - just give up"

<g> While LIKE is needed to find similar content, you still need to use
patterns to get a match, e.g.

Me.Filter = "[" & SelectedFieldName & "] Like '*" & SearchText & "*' "

btw, you should use Replace():

Me.Filter = "[" & SelectedFieldName & "] Like '*" & _
Replace(SearchText, "'", "''") & "*' "

mfG
--> stefan <--

 

[mcescher]

I am trying to add a "generic" search function to the main form of my
database that will allow the user to choose a field to search and enter a
string to search for.  To do this, I have added two controls.  The first is a
combo box whose source is the Field List from the table feeding the form. 
The second is a text box in which the user can type a string to be searched
for.

Here is how I want it to work:
1.  User selects the specific field to search in the combo box.  Thispart
works fine.
2.  User types in a text string in the text box (something like *ability or
%time or whatever).  Also works fine.
3.  User presses the "Search" button, and the database is filtered to show
only records for which the selected field matches the criterion.

I have tried using:

  Me.Filter = "[" & SelectedFieldName & "] Like '" & SearchText & "' "
  Me.FilterOn = True

but I always get zero records, even when the selected field is a text field.

I know this is probably harder than I want it to be.  Any suggestions will
be greatly appreciated.  Including "Trust me, it's too hard - just giveup"  


Eric

This might be a little far fetched, but if you put a break point on
the Me.Filter line, and then hover over the field names, are they
returning what you think they should be returning. I tried your code
with good success. None of my field names have spaces. Do yours?

Just a couple of things to check, hope they help,
Chris M.

 

[egun]

Thanks for your response. For test purposes, I have been typing the pattern
matching characters into the text box.

For example, I select "Title" from the field list, then type in "This is *"
in the text box. The resulting filter looks like:

Me.Filter = "[Title] Like 'This is the %'"

(I replace * with % because % is ANSI standard SQL)

Even though I have a record with a title "This is the title", the filter
operation returns no records.

hi,

Me.Filter = "[" & SelectedFieldName & "] Like '" & SearchText & "' "
Me.FilterOn = True

but I always get zero records, even when the selected field is a text field.

I know this is probably harder than I want it to be. Any suggestions will
be greatly appreciated. Including "Trust me, it's too hard - just give up"

<g> While LIKE is needed to find similar content, you still need to use
patterns to get a match, e.g.

Me.Filter = "[" & SelectedFieldName & "] Like '*" & SearchText & "*' "

btw, you should use Replace():

Me.Filter = "[" & SelectedFieldName & "] Like '*" & _
Replace(SearchText, "'", "''") & "*' "

mfG
--> stefan <--

 

[egun]

I did discover that if I put in an entire Title string, with no * or %, then
I do get any records with that EXACT title. Am I doing something wrong with
the way I have formed the filter string?

 

[egun]

Thanks for your reply. The field names are returning correct data. Yes,
many of them have spaces, and that is why I have the "[" and "]" enclosing
them. The field I am testing is named "Title", so spaces would not be an
issue there. I can get the search to work with an exact title match, but not
with a partial match (i.e. "This is the *" doesn't work, but "This is the
title" does).

 

[John Spencer (MVP)]

Did you try NOT replacing * with %?

Access does not use ANSI standard SQL unless you have checked the SQL Server
Compatible Syntax (ANSI 92) box on the Tables/Queries tab of the Options form.
Even then, I'm not sure what it does with filters - I would assume it would
follow ANSI92 standard.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[egun]

OMG - that worked! It's funny, because a question I asked yesterday resulted
in having to substitute % for * because I was using a SQL string in ADO and
was trying to use the *. I guess I'm surprised that Access won't accept the
"standard" internally, but I thank you for this little tidbit!

Now I can go make the rest of my search scheme work.

Eric