Update query to change case

[Bryan Ehlmann]

I am using update query to change the case of several
columns. UCase (table.field) works for changing all to
Upper Case. LCase (table.field) works for changing all to
lower case. Is there something that will change field in
columns to Proper Case? PCase (table.field) does not work.
Thanks for any help!

 

[Fredg]

Bryan,
StrConv() is what you're looking for.
Look it up in VBA Help files.

To update a field:

Update YourTable Set YourTable.FieldName = StrConv([FieldName],3);

Be aware that this will not properly capitalize all words and names, as some
names must always be in all caps (IBM, CBS, etc.), some never capitalized
(e.e. cummings), some have 2 capitals in the name (McDonald, O'Connor) and
some have a mixed set of capitalized names (van der Meer), as well as all
hyphenated names.

The only way to accurately handle names like these, that I know of, is to
have a table of exceptions, and DLookUp the table for that particular word
before changing it.
You would need to create a User Defined function to do all of this and
regularly maintain the list of names, adding new words as needed.

And, after all is said and done, there are still some words and names that
can be written both ways (O'Connor, O'connor, McDonald, Mcdonald) as well as
others whose capitalization depends on useage (ABC, abc, Xerox, xerox,
Access, access).