format text to "title" case

[yarra]

I have a report which contains (amongst other things) a list of names. The
problem is that the names are imported into the underlying table from several
different external data sources, and I don't have control over how the names
are entered into them. Some are all in upper case, some all in lower case,
and some in title case (eg "Jane Anne Smith"). I want them all to appear in
title case in my report. Other than re-entering the data in the table after
importing it, is there a way to achieve this? Can I set the format in my
report?

Thanks.

 

[Larry Linson]

This expression does what you want:

StrConv(LCase("ABEL bE gud"),vbProperCase)

You can use it in code, in the Query that is RecordSource for your Report or
in the Control Source of a Text Box on your Report. Replace the literal
string "ABEL bE gud" with a reference to the Field, Control, or Variable
containing the names of unknown format.

Larry Linson
Microsoft Office Access MVP

 

[John Spencer]

One thing Larry forgot to mention. If you plan to use the expression in a
query, then you cannot use the vb constant - vbProperCase. You must use its
value (3).

So in a query the expression would be
StrConv("AAAA vv CBV",3)
which would return Aaaa Vv Cbv

One problem is that a name like Oscar de la Hoya would be converted to Oscar
De La Hoya.

If you are willing to accept mixed case inputs as correct and only want to
change all lower and all upper case, you could test the value first and only
change if the value is all lower or all upper. Something like the
following:

IIF(StrComp(UCase(SomeField),SomeField,0) = 0 OR
StrComp(LCase(SomeField),SomeField,0) = 0, StrConv(SomeField,3),SomeField)


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Larry Linson]

One thing Larry forgot to mention. If you plan to use
the expression in a query, then you cannot use the vb
constant - vbProperCase. You must use its value (3).

So in a query the expression would be
StrConv("AAAA vv CBV",3)
which would return Aaaa Vv Cbv

Yes, I did overlook that little detail.

One problem is that a name like Oscar de la Hoya
would be converted to Oscar De La Hoya.

Yes, there are many, many variations on the theme of names -- that is why it
is so difficult to create a generally applicable algorithm to "normalize" or
"clean up" a list of names. It's complicated by names being so personal
that one person might in fact capitalize the "De La" and another use
lower-case, as does Oscar, while someone else may have combined them to
"Delahoya". About the best we can do is consider the names likely to be in
the set, choose some rules that work for "most" of them, and then clean up
by hand -- particularly unfortunate if you happen to have tens or hundreds
of thousands, or millions, of names in the list (as many databases do).

Larry Linson
Microsoft Office Access MVP