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
sensitive data. thanks for the help,
gary
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
K
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
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
J
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.
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.
K
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!
settings, and had overlooked them.
Thanks for the extra info!
G
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*";
to use somthing like:
.... WHERE fieldA Like "A*";
K
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.
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.
G
Gary Roach
Okay, looks like i'll have to do it the hard way! thanks for the help
gary
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
J
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##*"
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##*"
T
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
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
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.