to query for proper case text

  • Thread starter Thread starter NoelH
  • Start date Start date
N

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
 
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
..
 
Back
Top