Searching db records with accent marks

[nancicita]

In our db we can search by last name or company name. When searching by a person's name, a form pops up where we enter the last name. The event procedure for the form is to run a query in and in that query the search criteria for the lastname is

Like ([forms]![Person View_Search]![search_lastname] & "*&quot Or Is Null.

Some of our last names have accents like Pérez, but not everyone knows to use the accent mark, so if they search by "Perez" it will not find "Pérez" and vice versa. I want to figure out a way for the query to find both versions of Perez etc, no matter which one is used in the search form.

Any ideas?

 

[jryan224]

nancicita,

I don't know how to enter a letter with an accent mark myself, so hard to test any ideas I have.

If you really want users to be able to enter the accent, then check out the Like Operator/ visual basic for applications reference. There may be something there that may help.

this is an excerpt

******
Syntax

result = string Like pattern

The Like operator syntax has these parts:

Part Description
result Required; any numeric variable.
string Required; any string expression.
pattern Required; any string expression conforming to the pattern-matching conventions described in Remarks.
Remarks

If string matches pattern, result is True; if there is no match, result is False. If either string or pattern is Null, result is Null.
The behavior of the Like operator depends on the Option Compare statement. The default string-comparison method for each module is Option Compare Binary.

[charlist] Any single character in charlist.
[A–E] matches A, a, À, à, B, b, E, e.
******


It may take some extra programing but whatif your entered name is for example Perez.

Maybe programatically add [] around each letter? Then if I am understanding the above info the [P] would check for P and accented P as well as upper and lower case.


In a query I tried like "[y]ellow" and returned 'yellow' and 'Yellow".

Not sure if the name was entered with the accent if putting [] around tha accented letter would find the others. But if not, you could always do a loop that replaces any accented letter with the corresponding regular letter in your your code. So that P`erez would be changed to look for like "[P][e][r][e][z]".


Maybe this will help you,

If not, maybe as an option, you can prvent them from entering the accents in the first place. (We had to take this type of approach for not allowing the appostrophe in names as it wrecked the string syntax in sql statements. We use keypress and issue a backspace to erase an apostrophe as they type in the entry.)

JDemmer