case sensitivity in access

[Gary Roach]

how do i set case sensitivity on in access? i want to search on case
sensitive data. thanks for the help,

gary

 

[Ken Snell]

ACCESS does not support case sensitivity for searching/matching directly.
ACCESS treats all characters as upper case when using InStr, InStrRev, Like,
etc.

You can use the Asc() function to find specific upper or lower case
characters within a text string if you wish. In VBA you could loop through a
text string to find a lower case "a" (ASC value is 97) instead of the
generic upper case "A" (ASC value is 65). Something like this:


Dim strK As String
Dim intL As Integer
strK = "AaBbCcDd"
For intL = 1 To Len(strK)
If Asc(Mid(strK, intL, 1)) = 97 Then MsgBox "Found it - character " &
intL
Next intL

 

[John Spencer (MVP)]

Ken,
I'm sorry to disagree but, Instr and InStrRev and StrComp ALL can be case
sensitive (that is they will do a Binary compare).

Sample QUERY

SELECT *
FROM Table
WHERE FieldA = "aBc" AND Instr(1,FieldA,"aBc",0) = 1

This will return records where fieldA is equal "aBc", but now where fieldA is
equal to "ABC".

The reason I use the first statement in the where clause is that it seems as if
that filters the records down to just those containing "abc" and then the Instr
function is run against that limited set of records. I could be wrong, but that
is my guess.

 

[Ken Snell]

Absolutely correct, my good sir..... I have not had the need to use these
settings, and had overlooked them.

Thanks for the extra info!

 

[Gary Roach]

But what about doing the comparison in SQL to make use of wildcards. I want
to use somthing like:

.... WHERE fieldA Like "A*";

 

[Ken Snell]

You'd need to use a different approach. For your example:

WHERE Left([fieldA], 1) = Chr(65)

If you wanted lower case "a":

WHERE Left([fieldA], 1) = Chr(97)

If you wanted to duplicate "Like '*a*'":

WHERE InStr(1, [fieldA], Chr(97), 0 ) > 0

and so on.

 

[Gary Roach]

Okay, looks like i'll have to do it the hard way! thanks for the help

gary

You'd need to use a different approach. For your example:

WHERE Left([fieldA], 1) = Chr(65)

If you wanted lower case "a":

WHERE Left([fieldA], 1) = Chr(97)

If you wanted to duplicate "Like '*a*'":

WHERE InStr(1, [fieldA], Chr(97), 0 ) > 0

and so on.


--
Ken Snell
<MS ACCESS MVP>

But what about doing the comparison in SQL to make use of wildcards. I want
to use somthing like:

... WHERE fieldA Like "A*";


then
the character

 

[John Spencer (MVP)]

For that example.

WHERE FieldA like "A*" AND Instr(1,FieldA,"A",0) = 1

For FieldA contains

WHERE FieldA like "*A*" AND Instr(1,FieldA,"A",0) > 0

For FieldA ends

WHERE FieldA like "*A" AND Instr(1,FieldA,"A",0) = Len(FieldA)

It gets much more complex if you want to do something with "*[AB]*9##*"

 

[TC]

I'd still add: FieldA Like "a" to the WHERE clause. Otherwise, it has
no hope of using an index, and will do a full table scan.

Cheers,
TC

You'd need to use a different approach. For your example:

WHERE Left([fieldA], 1) = Chr(65)

If you wanted lower case "a":

WHERE Left([fieldA], 1) = Chr(97)

If you wanted to duplicate "Like '*a*'":

WHERE InStr(1, [fieldA], Chr(97), 0 ) > 0

and so on.


--
Ken Snell
<MS ACCESS MVP>

But what about doing the comparison in SQL to make use of wildcards. I want
to use somthing like:

... WHERE fieldA Like "A*";


then
the character