[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1486
  • Last Modified:

OPTION COMPARE DATABASE

What is OPTION COMPARE DATABASE

http://support.microsoft.com/kb/98227  Here is the KB article on this but it still doesn't explain it well.

I noticed in the MS Access Menu Tools-> Options->General Tab there is a combo box titled
New Database Sort Order
but this only has languages or the General selection

If mine is set to General how is my VBA compiler evaluating my string comparisons?  Is it using Binary or text comparisons?

Thanks
0
cations
Asked:
cations
2 Solutions
 
peter57rCommented:
Unless you use Option Compare Binary, your sort order will be text, - so it will ignore case.
If you change from General to something else (if you used a different language) then the General setting would come into play if you used Option Compare Database.

You can set different values for Option Compare in different modules; so if you have a bunch of procedures which require case-sensitive comparisons, for example, then you could put them into a module which uses Option Compare Binary.


0
 
cationsAuthor Commented:
So basically if I have the setting as "General"  Option Compare Database acts as Option Compare Text?

Thanks
0
 
harfangCommented:
Hello cations,

Please run the following from the immediate pane: RunTests. It uses one of the functions that accept a parameter to select the "option compare".

Option Explicit

Sub Compare(psA As String, psB As String, plM As VbCompareMethod)
    Select Case StrComp(psA, psB, plM)
    Case -1:    Debug.Print "  "; psA; " < "; psB
    Case 0:     Debug.Print "  "; psA; " = "; psB
    Case 1:     Debug.Print "  "; psA; " > "; psB
    End Select
End Sub

Sub Tests(plM As VbCompareMethod)
    Compare "a", "A", plM
    Compare "a", "Z", plM
    Compare "uex", "üz", plM
    Compare "üx", "uez", plM
End Sub

Sub RunTests()
    Debug.Print "Binary"
    Tests vbBinaryCompare
    Debug.Print "Text"
    Tests vbTextCompare
    Debug.Print "Database"
    Tests vbDatabaseCompare
End Sub

You should get something like this:

Binary
  a > A
  a > Z
  uex < üz
  üx > uez
Text
  a = A
  a < Z
  uex < üz
  üx > uez
Database
  a = A
  a < Z
  uex < üz
  üx > uez

As you see, 'text' and 'database' are identical. This is because I ran this with the "general" sort order. However the very last item is wrong, at least if you are creating a name index in German.

In German, 'ü' and 'ue' have exactly the same value, they are considered typographical variants, just like 'ß' and 'ss'. The 'ß' is treated like 'ss' in all sort orders, because this letter is only used in German, but 'ü' should not be equivalent to 'ue' in English or in French.

By changing to "German Phone Book", the last result of the test changes.

In short, 'text' = 'database' if you have selected the "general" sort order, but not if you have selected another.

The sort order becomes crucial in Chinese, for instance, as there are at least four widely accepted ways to sort ideograms a dictionary.

Cheers!
(°v°)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now