Convert to Proper or Title Case in Access 2003

[Jeff]

I am trying to convert a field that is in all CAPS to Proper Case. Ex: Field
Name is First Name, contents "ROGER". Desired way for First Name field to
show on query results is "Roger".

 

[fredg]

I am trying to convert a field that is in all CAPS to Proper Case. Ex: Field
Name is First Name, contents "ROGER". Desired way for First Name field to
show on query results is "Roger".

Back up your data first.
To change existing data you can run an Update query:
Update MyTable set MyTable.[FieldName] = strConv([FieldName],3)

That will change already existing data.

To assure newly entered data is also in Proper Case then
On the Form's control's AfterUpdate event code:
Me![ControlName] = StrConv([ControlName],3)

Note: this will incorrectly capitalize some words which contain more
than one capital, i.e. O'Brien, MacDonald, IBM, Jones-Smith, ABC,
etc., and incorrectly capitalize some words that should not have any
capitals, or whose capitalization depends upon usage, such as e. e.
cummings, abc, van den Steen.
Then some names can be capitalized in more than one manner, depending
upon personal preference, i.e. O'Connor and O'connor, McDaniels and
Mcdaniels, etc. are both correct.

You can create a table of exceptions and have the code use DLookUp
with a message if one of the exception words is found.

 

[KenSheridan via AccessMonster.com]

Use the StrConv function. This take a variety of arguments to covert text to
different case formats. Note that in a query you cannot use the
vbProperCase constant as you would in VBA, so must use its numeric value of 3
instead:

UPDATE [YourTable]
SET [First Name] = StrConv([First Name],3);

Look out for names like

Ken Sheridan
Stafford, England

 

[Marshall Barton]

I am trying to convert a field that is in all CAPS to Proper Case. Ex: Field
Name is First Name, contents "ROGER". Desired way for First Name field to
show on query results is "Roger".

Check VBA Help for the StrConv function.