Posted on 2007-10-20
Last Modified: 2013-11-27
What is OPTION COMPARE DATABASE  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?

Question by:cations
    LVL 77

    Accepted Solution

    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.


    Author Comment

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

    LVL 58

    Assisted Solution

    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:

      a > A
      a > Z
      uex < üz
      üx > uez
      a = A
      a < Z
      uex < üz
      üx > uez
      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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now