HOW to change CASE?

[Rob B.]

I purchased a commercial zip code database and all of the city names are in
UPPERCASE. How do I get them to display in title case, such as BOSTON to
Boston, or DES MOINES to Des Moines?

Thank you

Rob B.

 

[fredg]

I purchased a commercial zip code database and all of the city names are in
UPPERCASE. How do I get them to display in title case, such as BOSTON to
Boston, or DES MOINES to Des Moines?

Thank you

Rob B.

Look up StrConv() in VBA Help.
You can use an Update query to permanently change the data:
..... Back up your data first .....

Update YourTable Set YourTable.[CityField] = StrConv([CityField],3);

You will have to go through the data and tweak the some of the names
as this will incorrectly capitalize some city names, i.e. King of
Prussia (Pennsylvania) will become King Of Prussia, and Coeur d'Alene
(Idaho) will become Coeur D'alene, etc.

 

[David Anderson]

Also see this Microsoft KB article: http://support.microsoft.com/kb/815282
for a description of the Proper function.

I don't know how this compares with Fredg's suggestion. Perhaps StrConv is a
more up to date solution, but Proper works for me.

David

 

[David Anderson]

StrConv does indeed look like a better solution. Ref
http://support.microsoft.com/kb/298607/EN-US/

David

 

[Pete D.]

This works well but may require tweak as it is for peoples names
http://www.mvps.org/access/strings/str0008.htm

 

[Mike Mueller]

Either function listed will work for you

-however-

if you are using this db to generate snail mail, keep in mind that most
postal services (USPS, CanadaPost, etc) prefer that everything on the
address label is All-Caps.

You can store the data in whatever format you want, but I would probably
keep it as either all uppercase or all proper-case. Then on the forms and/or
reports I would do the conversion there to either 'human friendly' or "PO
friendly"