Give the user opportunity to customize search

[Guest]

I have been searching for something that makes it possible for the users of
my database to write different searc criterias in my forms. For example; if
they are searching by adress they now have to write the exact adress to find
anything. I would like it to be possible for the users to use the * symbol to
search for all adresses starting on e.g. via marc. I.e. to have the same
search function as in access. Is this possible. I found something similar
(see below) but the problem there is that the user cannot choose whether they
want to have all adresses when writing via marc or just the adresses that is
exactly via marc.

Thanks!

Andreas

Check into using the "Like" qualifier with wildcard symbols. In query
design mode, the select criterion would look similar to:

Like [enter search term] & "*"

In your example, entering "user1" would return
user1
user11
user12345
...
(anything starting with "user1")

 

[Allen Browne]

Andreas, you need to use a form to get this level of flexibility into your
searches.

For each search box, you can add a combo for choosing the operator. The
combo's Row Source Type will be ValueList, and its Row Source will be:
"Is", "Is Not", "Contains", "Not contain", "Starts with", "Ends with",
"Is Blank"
and so on.

Then in the code that build the Where string (the Filter for the form,
WhereCondition for OpenReport, or whatever), you can use the correct
operator.

Basic logic:

With Me.txtFindAddress
If Not IsNull(.Value) Then
Select Case Me.cboOpAddress
Case "Is"
strWhere = strWhere & "([Address] = """ & .Value & """)"
Case "Is Not"
strWhere = strWhere & "([Address] <> """ & .Value & """)"
Case "Contains"
strWhere = strWhere &"([Address] Like ""*" & .Value & "*"")"
Case "Starts with"
strWhere = strWhere &"([Address] Like ""*" & .Value & """)"
Case "End With"
strWhere = strWhere &"([Address] Like """ & .Value & "*"")"
Case "Is Blank"
wtrWhere = strWhere & "([Address] Is Null)"
End Select
End If
End With

 

[Ken Snell \(MVP\)]

I have a sample database that shows how to use various controls to filter a
report; the logic in the code would be the same for filtering a form:

http://www.cadellsoftware.org/SampleDBs.htm#FilterForm