.Seek does not honor Option Compare Binary

[ljpitre]

I have "Option Compare Binary" at the top of all my modules.

I noticed today that the .Seek "=", "ABC" does not follow binary rules.

that is if I have two records "ABC" & "abc" the seek would match on either it finds first. Indicating seek is case insensitive.

Is there a way to force the seek to be case sensitive?

 

[isladogs]

I've never used that statement in an Access module

Normally you should use:

Option Declare Database
Option Explicit

This is an extract from:
http://www.fmsinc.com/microsoftaccess/modules/options/index.html#OptionCompare

Option Compare specifies how string comparisons are evaluated in the module such as case sensitive vs. insensitive comparisons (e.g. should "A" = "a" be True or False?).

By default, Access/VBA uses:

Option Compare Database
This is a case insensitive comparison and respects the sort order of the database. In VB, which doesn't have the Database option, it's the same as the Text option:

Option Compare Text
That means, "A" = "a", which are both less than "B".

For exact (case sensitive) comparisons, so "A" is not the same as "a", use:

Option Compare Binary
If you are debugging code and confused because you can't understand seemingly valid text comparison failing when it works in another module, be sure to check the module's Option Compare setting. For instance, if strValue below is "YES", the evaluation below differs based on the Option Compare setting:

If strValue = "Yes" Then
In general, you should use the default Option Compare Database for your Access VBA code. If you need to make a case insensitive comparison, use the StrComp function with the vbBinaryCompare option:

StrComp(string1, string2, vbBinaryCompare)
That way you can move the code into any module and always have case sensitive comparisons without worrying about the Option Compare setting.

 

[MarkK]

Also, your Option Compare statement is scoped to the VBA module in which it appears, but when you call the DAO.Recordset.Seek method, you are calling a subroutine in a referenced type library whose codebase resides outside the scope of your module. DAO.Recordset.Seek knows nothing about your code, doesn't even know what language you are writing in.
hth
Mark