Field Formats

Hi all, hope you can help me.
I have a huge (and I mean huge - over 200,000 records) database that I use
for telephone and contact data among other things. One thing i want to be
able to do is to format a table field for Title Case only. Can this be done?
e.g I know that in the format field makes all entries in upper case and
changes all entries to lower case but is there a formula for title case, i.e.
initial capitals?
I await your response and thanks again.

DM

hi,

CJ1965 wrote:
One thing i want to be
able to do is to format a table field for Title Case only. Can this be done?
e.g I know that in the format field makes all entries in upper case and
changes all entries to lower case but is there a formula for title case, i.e.
initial capitals?
Take a look at

Function StrConv(String, Conversion As VbStrConv, [LocaleID As Long])

You may use it in an update query, but this function is not bullet proof
when used in conjunction with family names.


mfG
-- stefan --

On Thu, 28 Aug 2008 02:32:00 -0700, CJ1965
wrote:

Hi all, hope you can help me.
I have a huge (and I mean huge - over 200,000 records) database that I use
for telephone and contact data among other things. One thing i want to be
able to do is to format a table field for Title Case only. Can this be done?
e.g I know that in the format field makes all entries in upper case and
changes all entries to lower case but is there a formula for title case, i.e.
initial capitals?
I await your response and thanks again.

DM

You cannot do this with a Format property - it's simply not flexible enough.

What you can do is run an Update query actually updating the data in place -
i.e. changing "jim wilson" to "Jim Wilson". You would use the builtin
StrConv() function - you can open the VBA editor and see the help for it.

Running it from a Query you would need to use the literal value 3 for the
constant vbProperCase described in the Help: e.g.

UPDATE yourtable
SET LastName = StrConv([LastName], 3);

One warning: this function is itself rather limited and literalminded. It will
give results like "Mcdonald" where you want "McDonald", "O'brian" where
"O'Brian" would be correct, and "Evans-smith" where you want "Evans-Smith".

If you have a mixture of data in all lower case which you want "propered" and
mixed case which you want to leave alone, you can use the StrComp() function
to filter the data:

UPDATE yourtable
SET LastName = StrConv([LastName], 3)
WHERE StrComp([LastName], LCase([LastName]), 0) = 0;

--

John W. Vinson [MVP]

If you are willing to live with the few exceptions mentioned elsewhere
in-thread, you might not even need to do the conversion!

Instead, create a query that returns the converted value(s) and let the data
get entered however.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP


"CJ1965" wrote in message
...
Hi all, hope you can help me.
I have a huge (and I mean huge - over 200,000 records) database that I use
for telephone and contact data among other things. One thing i want to be
able to do is to format a table field for Title Case only. Can this be
done?
e.g I know that in the format field makes all entries in upper case and

changes all entries to lower case but is there a formula for title case,
i.e.
initial capitals?
I await your response and thanks again.

DM