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?

Who is Participating?
peter57rConnect With a Mentor Commented:
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.

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

harfangConnect With a Mentor Commented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.