Update any Mcsmith to McSmith, etc . . .

[Peter]

Hi gang,

I have a client who wants us to update a Last Name field to proper case for
any last name beginning with Mc. They want the 3rd character uppercased in
all such names. So any Mca . . ., Mcb . . . ., Mcc . . ., becomes McA . . .,
McB . . ., McC . . ., etc. You get the picture.

We are well aware that some names can actually be Mca . . . with the third
character retained in lower case. But the client wants all third characters
in upper case.

Anyway, I am look for a code that I could use to simply update the Last Name
field by running an update query.

Any ideas?

Thanks

Peter
ps - I have done a search on this subject, but the answers do not give me a
simple update query method . . .

 

[Jason Lepack]

UPDATE
[Your Table Name]
SET
[Name Field] = Left([Name Field],2) & UCase(Mid([Name Field],3,1))
& Mid([Name Field],4)
WHERE [Name Field] Like "mc*";

Replace [Your Table Name] and [Name Field] with the appropriate names.

Cheers,
Jason Lepack

 

[John W. Vinson]

I have a client who wants us to update a Last Name field to proper case for
any last name beginning with Mc. They want the 3rd character uppercased in
all such names. So any Mca . . ., Mcb . . . ., Mcc . . ., becomes McA . . .,
McB . . ., McC . . ., etc. You get the picture.

We are well aware that some names can actually be Mca . . . with the third
character retained in lower case. But the client wants all third characters
in upper case.

Anyway, I am look for a code that I could use to simply update the Last Name
field by running an update query.

UPDATE [tablename]
SET LastName = "Mc" & StrConv(Mid([LastName], 3), 3)
WHERE LastName LIKE "Mc*";

Backup the database first of course!

Mc is pretty safe - Mac is much riskier (there's a family named Machado here
in Parma; they're Basque not Celtic and are not part of the Hado clan!)

John W. Vinson [MVP]

 

[Peter]

Thanks Jason! Much appreciate it!

Peter

UPDATE
[Your Table Name]
SET
[Name Field] = Left([Name Field],2) & UCase(Mid([Name Field],3,1))
& Mid([Name Field],4)
WHERE [Name Field] Like "mc*";

Replace [Your Table Name] and [Name Field] with the appropriate names.

Cheers,
Jason Lepack

Hi gang,

I have a client who wants us to update a Last Name field to proper case for
any last name beginning with Mc. They want the 3rd character uppercased in
all such names. So any Mca . . ., Mcb . . . ., Mcc . . ., becomes McA . . .,
McB . . ., McC . . ., etc. You get the picture.

We are well aware that some names can actually be Mca . . . with the third
character retained in lower case. But the client wants all third characters
in upper case.

Anyway, I am look for a code that I could use to simply update the Last Name
field by running an update query.

Any ideas?

Thanks

Peter
ps - I have done a search on this subject, but the answers do not give me a
simple update query method . . .

 

[Peter]

Thanks John!

I did tell our client that the Mac should not be touched. They actually
listened

Peter

 

[Jason Lepack]

You're very welcome.

Cheers,
Jason Lepack

Thanks Jason! Much appreciate it!

Peter

UPDATE
[Your Table Name]
SET
[Name Field] = Left([Name Field],2) & UCase(Mid([Name Field],3,1))
& Mid([Name Field],4)
WHERE [Name Field] Like "mc*";

Replace [Your Table Name] and [Name Field] with the appropriate names.

Cheers,
Jason Lepack

- Show quoted text -