UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> FunctionLibrary:Comparing strings with accents    

Suppose we have a list of people from different parts of world and people may have different ways to spell one other's names and sorting names. This is an old problem faced by any database developers, possibly including Access developers. While Access allows us to specify which "Sort Order" (also known as Collations or Code Page in different contexts), this is at database level and cannot be changed - we would need to create a new database with the different sort order and import in the data. Obviously this can be cumbersome if we need to cope with different possible way to sort.

However, there is a workaround that we can use to provide one-off comparison even in a database that does not have the correct sort order. In a database using general sort order, doing this:

CODE
?Mueller = Müller
False

That is expected behavior for this Sort Order, but maybe we need to be sure we locate Mr. Müller even though we're searching using "Mueller". That would require German Phonebook collation. However, we can force a comparison using German collation by making use of undocumented feature of StrConv function. The third argument of StrConv normally is used to specify if the comparsion should be text, binary or unicode. However, we can provide Windows Locale ID in the third parameters and thus get the StrConv() to compare in that locale ID (in this case, German Phonebook collation).

CODE
Const LOCALE_GERMAN_PHONEBOOK As Long = &H10407

StrComp("Mueller", "Müller", LOCALE_GERMAN_PHONEBOOK)

Which will return 0 (e.g. the strings are equal as defined by the StrConv()) which is what we want.

Note: The Locale ID are given in hexadecimal - to use them in VBA we would omit the leading "0x" and replace it with "&H"

Edit Discussion
Custom Search


Thank you for your support!
This page has been accessed 2,335 times.  This page was last modified 21:38, 25 July 2010 by BananaRepublic.   Disclaimers