A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Convert to Proper or Title Case in Access 2003



 
 
Thread Tools Display Modes
  #1  
Old April 29th, 2010, 09:00 PM posted to microsoft.public.access.queries
Jeff
external usenet poster
 
Posts: 1,347
Default Convert to Proper or Title Case in Access 2003

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".
  #2  
Old April 29th, 2010, 10:19 PM posted to microsoft.public.access.queries
fredg
external usenet poster
 
Posts: 4,386
Default Convert to Proper or Title Case in Access 2003

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
  #3  
Old April 29th, 2010, 10:22 PM posted to microsoft.public.access.queries
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Convert to Proper or Title Case in Access 2003

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

  #4  
Old April 29th, 2010, 10:44 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Convert to Proper or Title Case in Access 2003

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]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.