CASE SENSITIVE?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a need to perform a query where I can find lower case
text. I have looked at the Help menu in Access and found some information,
but I can not get the query to work. Do you think you could have someone at
Springhouse email you an example of what the criteria in the query should
be. Listed is an example of what the query and the result I am looking for.
I know the criteria examples I am giving do not work because as I have them
they do not distinguish between upper and lower case letters. I hope this is
understandable!

RO Number Field
A4O01234
A4O01235
A4o01236
A4O01237
A4O01238
a4O01239

Query Criteria Example #1: Like "*o*" Result:
A40o1236
Query Criteria Example #2: Like "a*" Result:
a4O01239
Query Criteria Example #3: Like "A*" Result:
A4O01234, A4O01235, A4o01236, A4O01237 & A4O01238
 
Patty,

As you've discovered, Access isn't case sensitive. A question I would pose
to you would be are you looking to find these records to correct the case.
If that is what you are doing, then I would suggest a simple update query to
raise everything to upper case.

For example: UPDATE yourtable SET RONumber=Ucase(RONumber);

Mark
 
I know the criteria examples I am giving do not work because as I have them
they do not distinguish between upper and lower case letters.
Click to expand...

Access itself does not distinguish case. Searching for "a" finds both
"a" and "A", and there is no way to get around this except to use a
VBA function.

Mark's suggestion of just blindly updating all the fields to
UCase([field]) is probably the best bet if that's the eventual goal;
if however you need to make distinctions between "a34321" and "A34321"
you can use the StrComp function. A search criterion of

[fieldname] = "a34321" AND StrComp([fieldname], "a34321", 0) = 0

will find only the lower-case a instance.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
I did a double take on this, wondering why you used [fieldname] =
"a34321" at all, and not just StrComp([fieldname], "a34321", 0) = 0.

Then it dawned on me, You were probably hoping there might be an
index that could be used to narrow the search and thereby prevent a
table scan. Probably a good move, then, John. Subtle of you not to
mention it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I know the criteria examples I am giving do not work because as I have them
they do not distinguish between upper and lower case letters.
Click to expand...

Access itself does not distinguish case. Searching for "a" finds both
"a" and "A", and there is no way to get around this except to use a
VBA function.

Mark's suggestion of just blindly updating all the fields to
UCase([field]) is probably the best bet if that's the eventual goal;
if however you need to make distinctions between "a34321" and "A34321"
you can use the StrComp function. A search criterion of

[fieldname] = "a34321" AND StrComp([fieldname], "a34321", 0) = 0

will find only the lower-case a instance.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Then it dawned on me, You were probably hoping there might be an
index that could be used to narrow the search and thereby prevent a
table scan. Probably a good move, then, John. Subtle of you not to
mention it.
Click to expand...

Well... *oversight* of me not to mention it! Quite correct.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top