Search Function find exact words

[Jochen]

Hi,

I'm currently using a search function in my db that i found in these
newsgroups. Works perfectly. But I would like the user to be able to choose
to search for an exact string or to search for part of the data
Currently I use:

strWhere = strWhere & " AND " & "qry_Personen.Naam Like '*" & Me.Naam & "*'"

So when I enter "Pete", i get every entry that has "Pete" in it, so also
"Peters" and "Peterson",....

I have added a checkbox next to the field 'Naam', When this checkbox is true
i would like the searchfunction to search the exact string so i only get
"Pete" as a result

I tried the following:

If Checkexact = False Then
If Not IsNull(Me.Naam) Then
strWhere = strWhere & " AND " & "qry_Personen.Naam Like '*" & Me.Naam &
"*'"
End If
Else
If Not IsNull(Me.Naam) Then
strWhere = strWhere & " AND " & "qry_Personen.Naam = " & Me.Naam & ""
End If
End If

Problem is that it asks me to enter a Parameter value when i try to search

What am I doing wrong?

 

[Allen Browne]

If the Naam field will contains only one word, just use the = operator
instead of Like, and omit the wildcards:

strWhere = strWhere & " AND " & "qry_Personen.Naam = """ & Me.Naam &
""""

If the field could contain many words, but you only want to match whole
words, you will have to do a considerable amount more parsing. Here's a
place to start:
http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm

Rather than take that approach, though, it might be worth the effort to see
if the table should be redesigned. One of the goals of data normalization is
that each field is atomic, i.e. that it contains only one thing. If it
contains multiple things, there might be an alternative approach.

 

[Jochen]

Thanks, that worked great

I will see if i can normalize my data some more
I have the fields "Name" and "First Name"
Both can contain more than one word
the reason i wanted to be able to search for an exact string was actually
the field "First Name"
Because I entered First Name, Second Name and even Third Name all in that
same field. When i search for someones first name i also get results when his
second or third name mathces the search string.
Do you think I would profit from splitting the "First Name" field in 2 or 3
fields? I think 90% would contain nulls ( I don't know how it is in the USA
but here in Belgium, second names and third names aren't very common)

Thanks in advance

 

[Allen Browne]

What I would suggest is a field for Surname (or Naam, but not Name - that
one will cause problems), FirstName (since you often search on that), and
OtherNames (where any other names or aliases can go.)

The OtherNames could use Like, and not worry about the kind of issue you
hightlighed, because the occurance of multiple names there will be
reasonably rare.

So you could end up with an expression like this:
strWhere = strWhere & "((Naam = """ & Me.txt1 & _
""") OR (FirstName = """ & Me.txt1 & _
""") OR (OtherNames Like ""*" & Me.txt1 & "*"")) AND "

 

[Jochen]

Thanks,

I will redesign my table like you suggested.

Cheers