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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|