Accent Insensitive database querying
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!
Original Author: Carl Mercier
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! :)
Assumptions
You need to know how SQL queries work.
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!
Code
Function 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
Loading Comments ...
Comments
No comments have been added for this post.
You must be logged in to make a comment.