Field Formats

[CJ1965]

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

 

[Stefan Hoffmann]

hi,

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

 

[John W. Vinson]

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;

 

[Jeff Boyce]

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