Changing text to Title Case

  • Thread starter Thread starter Brad Smith
  • Start date Start date
B

Brad Smith

We have a membership database that has over 2000 members with most of the
name and address fields having been entered in UPPERCASE.

Is there any way I can automate a change on all these records to Title Case?

I'm very new to Access. BTW we're on Access 97 (I think!)

Any info would be greatly appreciated.

Thanks.
 
CurrentDb.Execute "Update [TableName] Set [FieldName] =
StrConv([FieldName],3)"

3 is the value for the constant vbProperCase, but in this
instance, you have to use the value.


Chris Nebinger
 
I don't have Access 97 running so I can't tell you what it might be able to
do. You may be able to use Excel. My version of Excel (2003) has a
function called PROPER that does what you want. For instance, if you can
export that table to Excel, you can use the PROPER funcion to format the
names. Then you could link the excel worksheet back to your mdb file and
use an update query to update your real table with the changes.

Not too elegent but it might do the trick.
Good Luck!

Kevin
 
Thanks Chris, but I'm a bit confused. I'm an absolute beginner to Access
(I'm probably in the wrong conference) and I'm not quite sure what to do
with...or how to execute....what you've written below.

Step by step would be fantastic (if you have the time and the inclination)

Many thanks
Brad


CurrentDb.Execute "Update [TableName] Set [FieldName] =
StrConv([FieldName],3)"

3 is the value for the constant vbProperCase, but in this
instance, you have to use the value.


Chris Nebinger
-----Original Message-----
We have a membership database that has over 2000 members with most of the
name and address fields having been entered in UPPERCASE.

Is there any way I can automate a change on all these records to Title Case?

I'm very new to Access. BTW we're on Access 97 (I think!)

Any info would be greatly appreciated.

Thanks.

.
Click to expand...
Click to expand...
 
Thanks Chris, but I'm a bit confused. I'm an absolute beginner to Access
(I'm probably in the wrong conference) and I'm not quite sure what to do
with...or how to execute....what you've written below.

Step by step would be fantastic (if you have the time and the inclination)
Click to expand...

PMFJI but you don't actually need any VBA code (as Chris suggests)
unless you actually mean that you want to "automate" the process (so
the user doesn't need to know anything about Access to do it).

If it's a one-shot operation, create a Query based on your table.
Select each field that you want converted from lower case or UPPER
CASE or MiXeD CAse to Proper Case. Change the Query to an Update query
by using the query type icon (or the Query menu option). A new line
will appear in the query grid labeled Update To:

On this line put

StrConv([fieldname], 3)

using the name of the field that you want converted.

Run the query using the ! icon on the toolbar and it should convert
all the values.

Note that it will convert properly capitalized names like MacDonald
and McKim to Macdonald and Mckim... which you might not want!

John W. Vinson[MVP]
(no longer chatting for now)
 
A thousand thanks John, I'll give it a whirl!



Thanks Chris, but I'm a bit confused. I'm an absolute beginner to Access
(I'm probably in the wrong conference) and I'm not quite sure what to do
with...or how to execute....what you've written below.

Step by step would be fantastic (if you have the time and the inclination)
Click to expand...

PMFJI but you don't actually need any VBA code (as Chris suggests)
unless you actually mean that you want to "automate" the process (so
the user doesn't need to know anything about Access to do it).

If it's a one-shot operation, create a Query based on your table.
Select each field that you want converted from lower case or UPPER
CASE or MiXeD CAse to Proper Case. Change the Query to an Update query
by using the query type icon (or the Query menu option). A new line
will appear in the query grid labeled Update To:

On this line put

StrConv([fieldname], 3)

using the name of the field that you want converted.

Run the query using the ! icon on the toolbar and it should convert
all the values.

Note that it will convert properly capitalized names like MacDonald
and McKim to Macdonald and Mckim... which you might not want!

John W. Vinson[MVP]
(no longer chatting for now)
Click to expand...
 
Hi Brad

The following function will do the job for you. Simply copy and paste this
into a new module. Or if you want you can use the StrConv function as per the
Access help file


Function Proper(x)
' Capitalize first letter of every word in a field.
' Use in an event procedure in AfterUpdate of control;
' for example, [Last Name] = Proper([Last Name]).
' Names such as O'Brien and Wilson-Smythe are properly capitalized,
' but MacDonald is changed to Macdonald, and van Buren to Van Buren.
' Note: For this function to work correctly, you must specify
' Option Compare Database in the Declarations section of this module.
'
' See Also: StrConv Function in the Microsoft Access 97 online Help.

Dim Temp$, C$, OldC$, i As Integer
If IsNull(x) Then
Exit Function
Else
Temp$ = CStr(LCase(x))
' Initialize OldC$ to a single space because first
' letter needs to be capitalized but has no preceding letter.
OldC$ = " "
For i = 1 To Len(Temp$)
C$ = Mid$(Temp$, i, 1)
If C$ >= "a" And C$ <= "z" And (OldC$ < "a" Or OldC$ > "z") Then
Mid$(Temp$, i, 1) = UCase$(C$)
End If
OldC$ = C$
Next i
Proper = Temp$
End If
End Function

Best Regards

Maurice St-Cyr
Micro Systems Consultants, Inc.
 
Back
Top