Field Formats

External Since: Aug 28, 2008 Posts: 1

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

External Since: Jan 29, 2004 Posts: 6313

On Thu, 28 Aug 2008 02:32:00 -0700, CJ1965 <CJ1965 DeleteThis @discussions.microsoft.com>
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]