Prev: Dave Ashish method of updating from previous record
Next: Replacing individual characters in a field to something else.
From: Jeff on 29 Apr 2010 16:00
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".
From: fredg on 29 Apr 2010 17:19
On Thu, 29 Apr 2010 13:00:02 -0700, Jeff wrote:

> 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.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
From: KenSheridan via AccessMonster.com on 29 Apr 2010 17:22
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

Jeff wrote:
>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".

--
Message posted via http://www.accessmonster.com

From: Marshall Barton on 29 Apr 2010 17:44
Jeff wrote:

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

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: Dave Ashish method of updating from previous record
Next: Replacing individual characters in a field to something else.