Convert to Proper or Title Case in Access 2003

[Jeff]

I am trying to convert a field that is in all CAPS to Proper Case. Ex: Field
Name is First Name, contents "ROGER". Desired way for First Name field to
show on query results is "Roger".

 

[fredg]

I am trying to convert a field that is in all CAPS to Proper Case. Ex: Field
Name is First Name, contents "ROGER". Desired way for First Name field to
show on query results is "Roger".

Back up your data first.
To change existing data you can run an Update query:
Update MyTable set MyTable.[FieldName] = strConv([FieldName],3)

That will change already existing data.

To assure newly entered data is also in Proper Case then
On the Form's control's AfterUpdate event code:
Me![ControlName] = StrConv([ControlName],3)

Note: this will incorrectly capitalize some words which contain more
than one capital, i.e. O'Brien, MacDonald, IBM, Jones-Smith, ABC,
etc., and incorrectly capitalize some words that should not have any
capitals, or whose capitalization depends upon usage, such as e. e.
cummings, abc, van den Steen.
Then some names can be capitalized in more than one manner, depending
upon personal preference, i.e. O'Connor and O'connor, McDaniels and
Mcdaniels, etc. are both correct.

You can create a table of exceptions and have the code use DLookUp
with a message if one of the exception words is found.

 

[espensu]

Dear Fred/Jeff,
I found your post, and like it, but I would very much like to learn more about the "DLookUp" and table for exceptions. I am often in the situation when I am "washing" / converting large amounts of data, in one specific table field "Name". I wish to run a code that would clean the strings to requirements. Typical it could be Proper Case or Centence case, while quite a few of exceptions needs to ignored by the update, such as "FO", or M/E, or "D/G" or "J.C.F.W." Also at the same time conversion from wrong case would be from "A.e" to "AE", A problem that I find sometimes is that the word may be in various positions, sometimes in the beginning, middle or end of a string. So in effect sometimes it is a try and fail to get it right, or as best possible. That is why a "from->to" table (or a simple to edit comma separated text file) would be very helpful, by looking up a value in column1, and replacing with value in column 2. A 3rd column would be nice to be able to turn it on/off with Y/N, in example:

col1, col2, col3
F.O., FO, Y
M/E, ME, Y
J.C.F.W, JCFW, Y
Aux.Engine, AE, Y
Aux Engine, AE, N
Main Engine, ME, Y
etc

today we have a delphi developed program doing it, but the exceptions are "hardcoded", and we have to compile a new program each time we wish to add new exceptions, Here is a sample of that delphi code, here string starting with a space. I think the way it works in that delphi program it first converts to Propercase "everything", and then converts the listed exceptions back to UCASE:

StrReplaceEnd(Tmp, ' G.s.', ' GS');
StrReplaceEnd(Tmp, ' G/s', ' GS');
StrReplaceEnd(Tmp, ' G.w.', ' GW');
StrReplaceEnd(Tmp, ' G/w', ' GW');
StrReplaceEnd(Tmp, ' Hfo', ' HFO');
StrReplaceEnd(Tmp, ' H.f.o.', ' HFO');
StrReplaceEnd(Tmp, ' H.f.o', ' HFO');
StrReplaceEnd(Tmp, ' H/f/o', ' HFO');

Hope you can do some magic, because it would be so much simpler to solve by using Access.

Thanks
Espensu