to query for proper case text

[NoelH]

Hi. I am looking at tables which come from an ODBC source. The data has
incorrctly been entered (in some cases) in the City Field, in Proper, Lcase &
Ucase. ( I can see how to change & save from lcase & Proper to ucase)
However, I'm looking for a query, to drive a report that will a) show the
data which satisfy each Ucase, Lcase + Proper, so that b) I can then sum
total each group, to report on the numbers errors.
Many thanks in advance for any help in this.
Noel

 

[John Spencer]

Try using the StrComp function in conjunction with the StrConv function

All upper case:
StrComp(City,StrConv(City,1),0) = 0

All lower case
StrComp(City,StrConv(City,2),0) = 0

Mixed case according to the rules of StrConv
StrComp(City,StrConv(City,3),0) = 0

Mixed Case : BaLTImore would not be detected as mixed case based on the
rules used by StrConv
StrComp(City,StrConv(City,1),0) <> 0 AND StrComp(City,StrConv(City,2),0) <>
0

In a query
SELECT Abs(Sum(StrComp(City,StrConv(City,1),0) = 0)) CountUpper
, Abs(Sum(StrComp(City,StrConv(City,2),0) = 0)) CountLower
, Abs(Sum(StrComp(City,StrConv(City,3),0) = 0)) CountProper
, Abs(Sum(StrComp(City,StrConv(City,1),0) <> 0 AND
StrComp(City,StrConv(City,2),0) <> 0) as Mixed
FROM YourTable

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..