Case-sensitivity in recordset.find

[Guest]

Is there a way to make the recordset.find method case sensitive? I've got a line of code that basically says, "recordset.find 'Name = bob'" but it's finding "Name = Bob". I'm not sure if that's actually better or not for my purpose, but I'd like the option of being able to decide one way or the other, instead of being forced into a choice

I've got "Option Compare Binary" at the top of the module. That seemed to take care of another comparison problem, but the .find method is still apparently not case sensitive

Thanks
Rich

 

[Dirk Goldgar]

Is there a way to make the recordset.find method case sensitive?
I've got a line of code that basically says, "recordset.find 'Name =
bob'" but it's finding "Name = Bob". I'm not sure if that's actually
better or not for my purpose, but I'd like the option of being able
to decide one way or the other, instead of being forced into a
choice.

I've got "Option Compare Binary" at the top of the module. That
seemed to take care of another comparison problem, but the .find
method is still apparently not case sensitive.

Thanks,
Rich

The Jet database engine used by Access is fundamentally
case-insensitive. If you use a DAO recordset to do your search, you can
use the StrComp function to perform a case-sensitive search, though it
won't be efficient because it can't use indexes:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)
rs.FindFirst "StrComp([Name], 'Bob', 0) = 0"
Debug.Print (Not rs.NoMatch)
rs.Close
Set rs = Nothing

I don't know if you can do this with an ADO recordset.

 

[Graham Mandeno]

Hi Rich

I haven't tried it in SQL, but the StrComp function should work:
rs.Find "StrComp([Name], 'bob', 0) = 0"

The third argument (0) indicates a binary (case-sensitive) compare. The
result returned will be 0 if the two strings are identical.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Is there a way to make the recordset.find method case sensitive? I've got

a line of code that basically says, "recordset.find 'Name = bob'" but it's
finding "Name = Bob". I'm not sure if that's actually better or not for my
purpose, but I'd like the option of being able to decide one way or the
other, instead of being forced into a choice.

I've got "Option Compare Binary" at the top of the module. That seemed to

take care of another comparison problem, but the .find method is still
apparently not case sensitive.

 

[TC]

Is there a way to make the recordset.find method case sensitive?
I've got a line of code that basically says, "recordset.find 'Name =
bob'" but it's finding "Name = Bob". I'm not sure if that's actually
better or not for my purpose, but I'd like the option of being able
to decide one way or the other, instead of being forced into a
choice.

I've got "Option Compare Binary" at the top of the module. That
seemed to take care of another comparison problem, but the .find
method is still apparently not case sensitive.

Thanks,
Rich

The Jet database engine used by Access is fundamentally
case-insensitive. If you use a DAO recordset to do your search, you can
use the StrComp function to perform a case-sensitive search, though it
won't be efficient because it can't use indexes:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)
rs.FindFirst "StrComp([Name], 'Bob', 0) = 0"
Debug.Print (Not rs.NoMatch)
rs.Close
Set rs = Nothing

I don't know if you can do this with an ADO recordset.

To make it use the (presumed) [Name] index:

rs.FindFirst "[Name]='Bob' AND StrComp([Name], 'Bob', 0) = 0"

TC

 

[Dirk Goldgar]

To make it use the (presumed) [Name] index:

rs.FindFirst "[Name]='Bob' AND StrComp([Name], 'Bob', 0) = 0"

I never thought of that! It sure looks like it would work.

 

[Guest]

Thanks all, but I can't seem to get it to work. As far as I can tell, the ADO recordset.Find method doesn't allow for using the strComp function. After trying different constructions, I found this in the "Access 2000 Developer's Handbook" by Getz, et. al.: "Criteria...can only consist of a single field name, a comparison operator, and a value. Find does not handle complex WHERE clause values.

The Jet engine is inherently case-insensitive? That seems really wonky to me. I mean, if I look at the value in the table, it's displayed as "Bob". So something different from "bob" was stored in the database. So if someone wants to find a record with "Bob" instead of "bob", but they type in "bob" by mistake, why would "Bob" get returned as OK? Is it actually converting every string it finds in any field in any table to lower case before operating on it? What a lot of overhead for something that I see as being undesirable far more often than desirable

In my case, I'm beginning to think it is actually desirable: the username is case-insensitive, but the password -- which I'm comparing once the record is found, using a a test which seems to adhere to the "option compare binary" at the beginning of the module -- is case-sensitive. But overall? I can't imagine that I'm going to be happy with this down the line in a different situation

Thanks
Ric

Rich

 

[Graham Mandeno]

Hi Rich

You're right! I'm afraid I suffer from AAS (ADO Avoidance Syndrome) and I
had forgotten the restrictions on .Find.

There are two ways around it. You could use .Filter instead, which does
allow for more complex WHERE clause expressions. Or, you code a loop using
..Find to get the next case-insensitive match and check with StrComp if the
resulting record matches the case-sensitive criterion. If not, then repeat
the loop until you get a match or EOF.

If you are going to use .Filter then TC is right. Including the = criterion
should be much faster if the field you are searching is indexed.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks all, but I can't seem to get it to work. As far as I can tell, the

ADO recordset.Find method doesn't allow for using the strComp function.
After trying different constructions, I found this in the "Access 2000
Developer's Handbook" by Getz, et. al.: "Criteria...can only consist of a
single field name, a comparison operator, and a value. Find does not handle
complex WHERE clause values."

The Jet engine is inherently case-insensitive? That seems really wonky to

me. I mean, if I look at the value in the table, it's displayed as "Bob".
So something different from "bob" was stored in the database. So if someone
wants to find a record with "Bob" instead of "bob", but they type in "bob"
by mistake, why would "Bob" get returned as OK? Is it actually converting
every string it finds in any field in any table to lower case before
operating on it? What a lot of overhead for something that I see as being
undesirable far more often than desirable!

In my case, I'm beginning to think it is actually desirable: the username

is case-insensitive, but the password -- which I'm comparing once the record
is found, using a a test which seems to adhere to the "option compare
binary" at the beginning of the module -- is case-sensitive. But overall?
I can't imagine that I'm going to be happy with this down the line in a
different situation.

 

[TC]

To make it use the (presumed) [Name] index:

rs.FindFirst "[Name]='Bob' AND StrComp([Name], 'Bob', 0) = 0"

I never thought of that! It sure looks like it would work.

I've never >actually tested< it, but I'm sure that it would. Jet's query
optimizer is way smart enough to say: "Hey, I feel an index coming on here!"


TC