How do you change text in a table to "Proper Case"

[Wm]

I have a table and the first column is a name that is in all caps. I need to
change to what I know as "Proper Case" where the first letter is caps.

Any ideas? Can this be done through the build function?

 

[Al Campagna]

Wm,
You could run an update query against the table with
StrConv([MyField], 3)
Or... just format the field to display that way.
Be aware that mcdonlad would yield Mcdonald, not McDonald.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[storrboy]

I have a table and the first column is a name that is in all caps. I need to
change to what I know as "Proper Case" where the first letter is caps.

Any ideas? Can this be done through the build function?

You could turn on the Auto Correct option in the tools menu or use the
following. Modify to suit.

Function CharacterCaps(svString As String) As String
Dim svTemp As String
On Error Goto Exit_Here
CharacterCaps=svString

svTemp=LCase(svString)
svTemp=UCase(Left(svTemp,1)) & Mid(svTemp,2)
CharacterCaps=svTemp

Exit_Here:
End Function

 

[Wm]

I'm not that familiar with Access - as stated - is this something you can do
in the "build" function? Where do you format the field. When I look at the
properties for the column - it does show format, but there are no selections
in there.

Wm

Wm,
You could run an update query against the table with
StrConv([MyField], 3)
Or... just format the field to display that way.
Be aware that mcdonlad would yield Mcdonald, not McDonald.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I have a table and the first column is a name that is in all caps. I need to
change to what I know as "Proper Case" where the first letter is caps.

Any ideas? Can this be done through the build function?

 

[Wm]

I have Access 2003 - not that familiar with it. I did look for the auto
correct function and could not find anything.

Where do I put the text you listed below. Can I use this in the "build"
function?

Wm

 

[John W. Vinson]

I'm not that familiar with Access - as stated - is this something you can do
in the "build" function? Where do you format the field. When I look at the
properties for the column - it does show format, but there are no selections
in there.

Create a new Query based on your table.

Change it to an Update query, using the Query menu option or the
query-type tool on the toolbar.

Select the field that you want proper-cased.

On the Update To line type

StrConv([fieldname], 3)

using your actual field name of course.

Run the query by clicking exclamation-point icon.


John W. Vinson [MVP]

 

[David P]

Saved me 10 hours of typing.

Thanks
--
David P

I'm not that familiar with Access - as stated - is this something you can do
in the "build" function? Where do you format the field. When I look at the
properties for the column - it does show format, but there are no selections
in there.

Create a new Query based on your table.

Change it to an Update query, using the Query menu option or the
query-type tool on the toolbar.

Select the field that you want proper-cased.

On the Update To line type

StrConv([fieldname], 3)

using your actual field name of course.

Run the query by clicking exclamation-point icon.


John W. Vinson [MVP]

 

[John W. Vinson]

Saved me 10 hours of typing.

<g> Glad to be of help.

If these are people's names, I'd suggest doing a search for names with
non-Proper capitalization: a criterion of

LIKE "Mc*" OR LIKE "Mac*" OR LIKE "*'*" OR LIKE "V[ao]N *"

will find a lot of names which the update query will have incorrectly
parsed (into such things as Mcphee for McPhee, Macleod for MacLeod -
which might in fact be correct, just got to ask the person, O'leary
for O'Leary, and Van schmidt for van Schmidt).

John W. Vinson [MVP]

 

[Wm]

Thanks, that worked like a champ!! These are names and I do realize that the
"Proper Case" is not 100% fool proof, but it sure gets you 98% of the way
there - especially on over 562,000 names!!!!

Saved me 10 hours of typing.

<g> Glad to be of help.

If these are people's names, I'd suggest doing a search for names with
non-Proper capitalization: a criterion of

LIKE "Mc*" OR LIKE "Mac*" OR LIKE "*'*" OR LIKE "V[ao]N *"

will find a lot of names which the update query will have incorrectly
parsed (into such things as Mcphee for McPhee, Macleod for MacLeod -
which might in fact be correct, just got to ask the person, O'leary
for O'Leary, and Van schmidt for van Schmidt).

John W. Vinson [MVP]

 

[John W. Vinson]

Thanks, that worked like a champ!! These are names and I do realize that the
"Proper Case" is not 100% fool proof, but it sure gets you 98% of the way
there - especially on over 562,000 names!!!!

If you can type 562,000 names in ten hours... I suspect your name is really
Clark Kent and you have a day job with the Planet!

John W. Vinson [MVP]

 

[Al Campagna]

storrboy,
That would work for only a single name like SMITH.
I think the OP has BOB SMITH or maybe even BOB G. SMITH.
Also, I have heard some real problems in most Access versions with having AutoCorrect
turned on.
I always turn that off...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[storrboy]

I thought that was Name Auto Correct and that it was a different from
Auto Correct. Maybe I'm wrong.
Point taken about the two names, I didn't really think that far into
it.

 

[Al Campagna]

Ah... my turn to misinterpret. :-D
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."