Option Compare Database

[Jack Leach]

Hello.

I've recently saw a post of some code that utilized the Option Compare Text
statement, and led me to do some investigation on what this really means.
I'm a bit stuck on a full understanding of Comapre Database though.

from help:
Option Compare Database can only be used within Microsoft Access. This
results in string comparisons based on the sort order determined by the
locale ID of the database where the string comparisons occur.

What is the default sort order a database (I assume the locale ID is default
depending on country). Is this a hybrid of both the Binary and Text compares
(Compare Database apparently will always be case-insensitive)? I can't seem
to find any deeper information on this.


I'm considering switching to Compare Binary as a default for certain
modules, but not exactly sure how the sort order will effect other areas of
my code, as I can't seem to find what exactly the sort order is on Compare
Database (my only reason for using Binary would be case-sensitivity, but am
not sure how the sort order will effect anything else).



Also, when using testing out the strcomp() function, which is supposed to
default to vbBinaryCompare, "A" and "a" return the same... I thought binary
was supposed to read these as different? Here's some immediate window lines
I'm getting...


?strcomp("A", "a", vbBinaryCompare)
-1
?strcomp("A", "a")
0

the default compare for strcomp() is supposed to be vbBinaryCompare (at
least that's what the argument popup says when entering info in the
function). If I were to change my Option Compare statement to effect the
results of this function, it makes the usuage of the function in the first
place a bit of a moot point... (of course, this if fairly trivial, as one
could always explicitly enter the vbBinaryCompare to make sure).

Just curious... I don't care for things I don't know and can't seem to find
what the sort order for Compare Database really is (and if it has any effect
on the default compare method for the strcomp() function).

Thanks for any input.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Alex Dybenko]

Hi,
I think it came from non-unicode time, in Access 97 and before we had to set
database sort order in options to russian, else we got wrong sort order
(russian and european characters occupy same "places" in non-unicode char
map). And even now, for some languages you have to set database order. So
this is what Option Compare Database. And Option Compare Text based on
system locale.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Jack Leach]

I'll have to do some research on character mapping... this is relatively new
to me.

The other day was the first time I'd ever seen anyone use anything other
than Compare Database, and got me wondering.

In the past I've gone through some steps to compare strings in a case
sensitive comparison (no biggie really), and the only place I've run into
where I wouldn't want case sensitivity is for user-driven searches (in which
case I could keep all form class modules as Compare Database).

Option Compare Binary seems like it may be a way to be more explicit with
code (which I'm a big fan of... I've recently taken to Option Base 0, just in
case). I suppose none of this will amount to a hill of beans when it's all
said and done though.

I'll toy around with unicode/non-unicode char maps and see if I can't put my
mind to rest with it over the next couple of weeks

Thanks!
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Tom van Stiphout]

On Wed, 20 May 2009 03:41:01 -0700, Jack Leach <dymondjack at hot mail
dot com> wrote:

I think you should always use Option Compare Database, because then
your VBA code behaves the same way as your queries do.
On the rare occasion you need something different typically a function
like StrComp can come to the rescue.

-Tom.
Microsoft Access MVP