ACC: Explanation of "Option Compare Database" Statement

Last reviewed: August 29, 1997
Article ID: Q98227

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

The description of the Option Compare Database statement in the Microsoft Access "User's Guide" is not very clear. The following is the description from the guide:

   Comparison is based on the characters' relative position according to
   the sort order specified when the database was created or compacted.

MORE INFORMATION

The Option Compare statement can be used to make comparisons based on binary value, text value, or values that are defined in the Options dialog box. (To modify the Options dialog box, click Options on the View menu.)

For example, if you use the Option Compare Binary statement, the expression "a" > "Z" would be true, because the ASCII value of "a" is greater than the ASCII value of "Z". However, if you use the Option Compare Text statement, the expression would be false, because "Z" comes after "a" in the alphabet. If you use the Option Compare Database statement, the expression will be true or false depending on the New Database Sort Order setting you select in the Items box of the Options dialog box.

The Option Compare statement can also affect the results of the Instr() function. For example, the following function finds the first tab character in a string:

   Function FindTab (mystr$)
      FindTab = InStr(1, mystr$, Chr(9))
   End Function

If mystr$="1<space>abc<tab>ABC", the InStr() function will return the following results, depending on which Option Compare statement is used:

   Option Compare Database: 2
   Option Compare Text:     6
   Option Compare Binary:   6

For another example, the following function returns the position of the first uppercase 'c' in the string:

   Function FindEm (mystr$)
      FindEm = InStr(1, mystr$, "C")
   End Function

If mystr$="1<space>abc<tab>ABC", the function will return the following results, depending on which Option Compare statement is used:

   Option Compare Database: 5
   Option Compare Text:     5
   Option Compare Binary:   9

Keywords          : kbprg PgmDecl PgmOthr SynGnrl
Version           : 1.0 1.1 2.0 7.0
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbdocerr

================================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.