Using Text Comparison Options

Tests of text data in fields of tables, query result sets, and Recordset objects against String or Variant text data in modules depends on the value of the Option Compare... statement, which appears in the Declarations section of the Utility Functions module. To determine how text comparisons are made in the module, you can use any of the following statements:

• Option Compare Binary comparisons are case sensitive. Lowercase letters are not equivalent to uppercase letters. To determine the sort order of characters, Access uses the character value assigned by the Windows ANSI character set.

• Option Compare Text comparisons are not case sensitive. Lowercase letters are treated as the equivalent of uppercase letters. For most North American users, the sort order is the same as Option Compare Binary, ANSI. Unless you have a reason to specify a different comparison method, use Option Compare Text.

• Option Compare Database comparisons are case sensitive, and the sort order is that specified for the database.

Access adds Option Compare Database to the Declarations section when you create a new module, overriding the default. Binary and Database are keywords in VBA, but these words don't have the same meaning when used in the Option Compare... statement. For compatibility with changes in possible future releases of Access, you should not use Compare or Text as names of variables.