Change text from all caps to sentence case

[crawford]

Is there an easy way to change fields in Access 2002 from all caps to
sentence case?

 

[fredg]

Is there an easy way to change fields in Access 2002 from all caps to
sentence case?

What is sentence case?
Only the first letter in a sentence capitalized?
What if the field contains text like:
"I worked for MacDonald's before I took a job with IBM."
What would you like to see in the above sentence?
Do you mean a field with just one word?
What would you like to see if the word is "IBM"?

To change a sentence to First letter capitalized, every thing else in
lower case:
[FieldName] = UCase(Left([FieldName],1)) & LCase(Mid([FieldName],2))

To Capitalize Every Word In A Field:
[FieldName] = StrConv([FieldName],3) which will not correctly display
words or names which should have more than one capital in them, nor
words which should not have a capital in them, i.e. van den Steen.

 

[Andi Mayer]

Is there an easy way to change fields in Access 2002 from all caps to
sentence case?

something like:

Sub changeFieldNamesToProperCase()
Dim tbl As DAO.TableDef
Dim db As DAO.Database
Dim fld As DAO.Field
Set db = CurrentDb
Set tbl = db.TableDefs("table1")
For Each fld In tbl.Fields
fld.Name = StrConv(fld.Name, vbProperCase)
Next fld
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing
End Sub

 

[crawford]

When the database was set up EVERYTHING was all caps. I use mail merge
frequently in Word and must change the names and addresses once the
information is merged. I want to change the database so this won't be
necessary.

Is there an easy way to change fields in Access 2002 from all caps to
sentence case?

What is sentence case?
Only the first letter in a sentence capitalized?
What if the field contains text like:
"I worked for MacDonald's before I took a job with IBM."
What would you like to see in the above sentence?
Do you mean a field with just one word?
What would you like to see if the word is "IBM"?

To change a sentence to First letter capitalized, every thing else in
lower case:
[FieldName] = UCase(Left([FieldName],1)) & LCase(Mid([FieldName],2))

To Capitalize Every Word In A Field:
[FieldName] = StrConv([FieldName],3) which will not correctly display
words or names which should have more than one capital in them, nor
words which should not have a capital in them, i.e. van den Steen.

 

[John Vinson]

When the database was set up EVERYTHING was all caps. I use mail merge
frequently in Word and must change the names and addresses once the
information is merged. I want to change the database so this won't be
necessary.

If it's names and addresses, rather than narrative text; and if you can
accept Having Every Word Capitalized, Even Names Like Maccarthy And De Los
Angeles, you can run an update query updating each field to

Strconv([fieldname], 3)

See the online help for StrConv by opening the VBA editor and searching Help.

John Vinson/MVP

 

[James A. Fortune]

Thanks to previous posts I've been able to convert
PIERCE, BENJAMIN FRANKLIN (one column)
to
Pierce Benjamin Franklin (two columns)

With this code
FName:StrConv(Right$([SName],Len([SName])- InStr(1,[SName],",")-1),3)
LName:StrConv(Left$([SName],InStr(1,[SName],",")-1),3)

However it doesn't correctly capitalize the second name in a hypenated
name, so I get this:
Smith-barney Susan
instead of
Smith-Barney

See if:

LName:IIf(InStr(StrConv(Left$([SName],InStr(1,[SName],",")-1),3),"-")=0,
StrConv(Left$([SName],InStr(1,[SName],",")-1),3),StrConv(Left$([SName],InStr(1,[SName],"-")-1),3)
& "-" & StrConv(Mid$([SName],InStr(1,[SName],"-") + 1,
InStr(1,[SName],",") - InStr(1,[SName],"-") - 1), 3))

handles all the cases you have. It looks to see if there's a hyphen.
If so, it breaks the name into two parts, applies the StrConv to each
part and puts the hyphen back; otherwise it uses what you had before.

James A. Fortune

 

[James A. Fortune]

I don't suppose there's a way to add formatting for Mc* names and Mac*?

Well, if you don't have BOTH a hyphen and a space in the last name
(e.g., MC TAVISH-O LEARY, ERIN) you could replace

StrConv(Left$([SName],InStr(1,[SName],",")-1),3)

with a very similar IIF that looks for a space and breaks it into two
pieces that would effectively capitalize the part of a last name after a
space, but that's not what I've done in the past. I'll see if I can dig
out an old database and possibly come up with a better method.

James A. Fortune

 

[James A. Fortune]

with a very similar IIF that looks for a space and breaks it into two
pieces that would effectively capitalize the part of a last name after a
space, but that's not what I've done in the past. I'll see if I can dig
out an old database and possibly come up with a better method.

James A. Fortune

It turns out the way I did it before was for a different problem. There
was one field that held the entire name. What I did was to break up
that single field into first and last name. I used a table with about
30 exceptions such as Van, Mc, O and Der. I also had to ignore Jr.,
Sr., III etc.

James A. Fortune