by Karabunga (2 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: Visual Basic 3.0
Difficulty: Advanced
Date Added: Wed 3rd February 2021
Rating: 
(8 Votes)
Since MS-Access doesn't support accent insensitive queries by itself (MS SQL Server does as far as I know), I had to create a function that would fix the problem. With this function, it is possible to turn any SQL query into an accent insensitive query. With a few little modifications, it works great with ASP too!
Inputs
Example: STRSQL = "SELECT * FROM MyTable WHERE animal LIKE '%" & AccIns("ELEPHANT") & "%'"
This will return any record where animal = Élephant, Elephant, éléphant, eléphant, etc. You get the picture. Now have fun! :)
Assumes
You need to know how SQL queries work.
Code Returns
An accent insensitive string to use against a database.
Side Effects
My friend's computer exploded last time he used this function, so watch out!
VBcodersFunction AccIns(Str As String) As String
Dim CurLtr As String * 1
For x = 1 To Len(Str)
CurLtr = Mid(Str, x, 1)
Select Case CurLtr
Case "e", "é", "è", "ê", "ë", "E", "É", "È", "Ê", "Ë"
AccIns = AccIns & "[eéèêëEÉÈÊË]"
Case "a", "à", "â", "ä", "A", "À", "Â", "Ä"
AccIns = AccIns & "[aàâäAÀÂÄ]"
Case "i", "ì", "ï", "î", "I", "Ì", "Ï", "Î"
AccIns = AccIns & "[iïîìIÏÎÌ]"
Case "o", "ô", "ö", "ò", "O", "Ô", "Ö", "Ò"
AccIns = AccIns & "[oôöòOÔÖÒ]"
Case "u", "ù", "û", "ü", "U", "Ù", "Û", "Ü"
AccIns = AccIns & "[uûüùUÛÜÙ]"
Case "c", "ç", "C", "Ç"
AccIns = AccIns & "[cCçÇ]"
Case Else
AccIns = AccIns & CurLtr
End Select
Next
End Function
