PROPER function in Query?

  • Thread starter Thread starter Microsoft
  • Start date Start date
M

Microsoft

Greetings,

I have a big project of fixing a database that was created by another that
contains many different tables from lots of different sources. One of my
problems is that some tables are in ALL CAPS, some are in all lower case,
some are in Proper Case and some are mixed. I cannot figure out the syntax
for an update query (I think this is the route I need to go) to change them
all to Proper Case. For instance I have tried to make a calculated field -
Exps1:PROPER([firstname]) -
This returns and error that the PROPER is and undefined function.

I guess first I need to know if it is worth the trouble to change the tables
or deal with it in forms and reports. Or do I need to do the query and if
so how?

Any thoughts on how I can fix this?

Best regards,
Scott B
 
There is no function named Proper in Access.
There is the StrConv() function, which will capitalize the first letter of
each word.
So to change JOHN JONES TO John Jones you would write (in a query):

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

Look up the function in VBA help.

Be aware, this will not correctly capitalize some names.
Names like McDaniels, O'Brien, etc. will not show the second capital letter.
Some names (or parts of a name) which must be all capitals,
ABC Corp., IBM, etc, will incorrectly become Abc Corp., or Ibm.

There is a way around most of this, by using a table of exceptions.
However, even then, there are some names which are capitalized
differently, depending upon usage or personal preference,
O'Brien and O'brien, O'Connor and O'connor,
or e.e. cummings, which is (by the authors preference) always lower case.
 
Fred,

Worked great. Thanks for the help.

Best regards,
Scott B


Fredg said:
There is no function named Proper in Access.
There is the StrConv() function, which will capitalize the first letter of
each word.
So to change JOHN JONES TO John Jones you would write (in a query):

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

Look up the function in VBA help.

Be aware, this will not correctly capitalize some names.
Names like McDaniels, O'Brien, etc. will not show the second capital letter.
Some names (or parts of a name) which must be all capitals,
ABC Corp., IBM, etc, will incorrectly become Abc Corp., or Ibm.

There is a way around most of this, by using a table of exceptions.
However, even then, there are some names which are capitalized
differently, depending upon usage or personal preference,
O'Brien and O'brien, O'Connor and O'connor,
or e.e. cummings, which is (by the authors preference) always lower case.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Microsoft said:
Greetings,

I have a big project of fixing a database that was created by another that
contains many different tables from lots of different sources. One of my
problems is that some tables are in ALL CAPS, some are in all lower case,
some are in Proper Case and some are mixed. I cannot figure out the syntax
for an update query (I think this is the route I need to go) to change them
all to Proper Case. For instance I have tried to make a calculated field -
Exps1:PROPER([firstname]) -
This returns and error that the PROPER is and undefined function.

I guess first I need to know if it is worth the trouble to change the tables
or deal with it in forms and reports. Or do I need to do the query and if
so how?

Any thoughts on how I can fix this?

Best regards,
Scott B
Click to expand...
Click to expand...
 
Back
Top