accents

[gabrielsoy]

I have a table with items from a large inventory. the user enters a search string, and I do a query on the table. Works O.K.

The problem is, some of the words in the table have accents (Rubèn, Avión, agüero), and the user can enter them with or without the accent.

Questions:
1-Does Access have a way to search ignoring accents, as it can ignore upper/lower case letters?
2-Is there a wild card in which I can enter several letters as equivalent? (something like search for 'Rub[eéèë]n', in which the fourth character could be e, or é, or è, or ë).
3-If there isn't 1- and 2-, any VBA reusable code?

Many thanks!

 

[gemma-the-husky]

easiest way might be to map the name to a soundex value, and then search for the equivalent soundex code

 

[georgedwilkinson]

You can also try a Jaro-Winkler algorithm which will give you an approximate match.

Soundex is an excellent too but will ignore vowels unless the vowel is the first letter in the word. It also only checks 3 consonants after the first letter.

Also, look into regular expressions. You can use regular expressions in your where clause. It would be very similar to your example:

WHERE FirstName like [B][I]'Rub[eéèë]n'[/I][/B]