case sensitivity in access

  • Thread starter Thread starter Gary Roach
  • Start date Start date
G

Gary Roach

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

gary
 
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
 
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.
 
Absolutely correct, my good sir..... I have not had the need to use these
settings, and had overlooked them.

Thanks for the extra info!
 
But what about doing the comparison in SQL to make use of wildcards. I want
to use somthing like:

.... WHERE fieldA Like "A*";
 
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.
 
Okay, looks like i'll have to do it the hard way! thanks for the help

gary

Ken Snell said:
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>


Gary Roach said:
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
Click to expand...
Click to expand...
 
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##*"
 
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


Ken Snell said:
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>


Gary Roach said:
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
Click to expand...
Click to expand...
 
Back
Top