Searching for Lowercase string

M

masta_d_funk

I am using Access 2000 and i have a database in which i would like to
search for all items that have the description in lowercase.
 
J

John Vinson

I am using Access 2000 and i have a database in which i would like to
search for all items that have the description in lowercase.
Click to expand...

This is trickier than it might seem, since Access searches are not
case sensitive. You can use the VBA function StrComp() for this
purpose, though:

SELECT * FROM yourtable
WHERE StrComp([Description], LCase([Description]), 0) = 0;

This will do a binary comparison (the first 0) of the actual contents
of the Description field, with those contents forced to lower case; if
they are identical the functionwill return 0.

John W. Vinson[MVP]
 
M

masta_d_funk

Thank you for your help. But it seems to not be displaying the values I
am after. Some items in the database contains lowercase. Is it possible
to search for those particular items?
 
J

John Vinson

Thank you for your help. But it seems to not be displaying the values I
am after. Some items in the database contains lowercase. Is it possible
to search for those particular items?
Click to expand...

The code as posted should do just that. Are you looking for records
where the entire string is in lowercase, with no uppercase letters at
all (which my suggested code should find), or are you looking for
records containing even a single lowercase letter? I.e.:

1 absce
2 Abdef
3 ABCDE
4 ABCDx

Which records should be "hits"?


John W. Vinson[MVP]
 
M

masta_d_funk

John,

Im looking for items that have "Abcdef". So the string contains both
lower and uppercase characters.
 
R

Ron2005

And then what are you going to do?

Should they all be lower or Upper but not mixed?

There may be an easier way if we know the consequences.

Ron
 
M

masta_d_funk

Ron,

Im just looking at how many items there are that contains a lowercase
string. Not planning to do anything with it at this point. But if there
is a SQL query that allows me to search for only those items that
contains lowercase string.
 
J

John Vinson

John,

Im looking for items that have "Abcdef". So the string contains both
lower and uppercase characters.
Click to expand...

Ok:

use a calculated field

StrComp([fieldname], UCase([Fieldname]), 0)

and use a criterion of <> 0 on it. If the string is already all upper
case it will be identical to its UCase(); if it contains even a single
lower case letter, it will be different.

John W. Vinson[MVP]
 
M

masta_d_funk

John

Thank you so much for your help. It displayed exactly what i wanted.

Thank you for your help and have a good new year.
 
R

Ron2005

Glad you got something that worked.
The reason I was asking was that you could force all of them to be
uppercase at the point of input.
In the beforeupdate event put me.fieldname = UCase(me.fieldname)

Ron
 
Top