ACC: Explanation of "Option Compare Database" Statement
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
Additional query words:
Keywords : kbprg
Version : WINDOWS:1.0,1.1,2.0,7.0
Platform : WINDOWS
Issue type :