Case issues

S

Susie

We have a database where data is imported from a registration vendor. We
have names that come in all "case" imaginable. We may have all caps, all
lower, a combo, etc. Is there a function in Access that will take a name,
and create consistent "case"? For example,

Imported Data: CARLOS COSCIA
Desired Result: Carlos Coscia

Thank you for your help.

Susie
 
J

John Vinson

We have a database where data is imported from a registration vendor. We
have names that come in all "case" imaginable. We may have all caps, all
lower, a combo, etc. Is there a function in Access that will take a name,
and create consistent "case"? For example,

Imported Data: CARLOS COSCIA
Desired Result: Carlos Coscia

Thank you for your help.

Susie
Click to expand...

Yes: StrConv(). See its online help with the VBA editor open.

For Proper Case, use an Update query updating the field to

StrConv([fieldname], 3)

Note that this will update a properly-formatted name like "Ian
MacDonald" or "Luis de la Torre" to "Ian Macdonald" and "Luis De La
Torre" respectively, but that sounds better than what you've got.

John W. Vinson[MVP]
 
G

Graham Mandeno

Hi Susie

The "built-in" way to do this is by using the StrConv function:

StrConv("CARLOS COSCIA", vbProperCase)
gives
Carlos Coscia

However, with names there are always exceptions that give rise to
difficulty. For example:

Ronald Mcdonald should be "McDonald"
Richard O'brien should be "O'Brien"
Walter De La Mare should be "de la Mare"

Some of these exceptions can be fixed automatically with lookup tables and
complex code, but others just cannot. For example, some people like to
spell "MacDonald", while others prefer "Macdonald". And then there are the
bizarre preferences like "e e cummings".

How far you go depends on how much time you want to put into the solution.

BTW, if you are using StrConv in a query, it will not understand the
built-in constant vbProperCase, so use the value 3 instead.
 
Top