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]

On Thu, 10 Feb 2005 08:17:06 -0800, crawford wrote:

> 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.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Andi Mayer]

On Thu, 10 Feb 2005 08:17:06 -0800, "crawford"

<craw...@discussions.microsoft.com> wrote:

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
---
If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

[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.

[John Vinson]

"crawford" wrote:

> 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

[byerssha]

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

Can someone help me out with this? Thanks.

--
byersshaPosted from http://www.pcreview.co.uk/ newsgroup access

[James A. Fortune]

byerssha wrote:
> 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

[byerssha]

That did the trick! Thank you very much.

[byerssha]

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

[James A. Fortune]

byerssha wrote:
> 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]

James A. Fortune wrote:

> 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