Capitalizing names in a field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a name field. The table has approx. 2000 records. I need to convert
all of the john smith names to John Smith i.e. capitalize the first character
of each word. How can I accomplish this?

Thanks
 
I have a name field. The table has approx. 2000 records. I need to convert
all of the john smith names to John Smith i.e. capitalize the first character
of each word. How can I accomplish this?

Thanks
Click to expand...

Note: Having both names in one field is not a good way to set up your
database.
Have a FirstName field and a LastName field. It is much easier to
concatenate the two fields into John Smith than to separate John Smith
into it's separate parts later on.

You can use the StrConv() function to change the text to proper case.

To update a field:

Update YourTable Set YourTable.FieldName = StrConv([FieldName],3);

Be aware that this will not properly capitalize all words and names,
as some names must always be in all caps (IBM, CBS, etc.), some never
capitalized (e.e. cummings), some have 2 capitals in the name
(McDonald, O'Connor) and some have a mixed set of capitalized names
(van der Meer), as well as all hyphenated names.

One way to accurately handle names like these, is to have a table of
exceptions, and DLookUp the table for that particular word before
changing it.
You would need to create a User Defined function to do all of this and
regularly maintain the list of names, adding new names as needed.

And, after all is said and done, there are still some words and names
that can be written both ways (O'Connor, O'connor, McDonald, Mcdonald)
as well as others whose capitalization depends on useage (ABC, abc,
Xerox, xerox, Access, access).
 
Back
Top