[MS Access] Proper case with "Mc" names

[CBlend]

I have customer data I've imported into Access, and one of my Update queries converts the person's last name to proper case (so that SMITH becomes Smith, for example.)

In the "Update To:" part of the grid for this field, I have this function:
StrConv([LastName],3)

The problem is when Access encounters a "Mc" name, such as "MCCLOSKEY". The StrConv function gives me "Mccloskey", but I would like "McCloskey". Has anyone else done this? Please?

I tried using the Expression Builder to make a function that looks at the first two letters of the last name, so that if it is "MC", then concatenate "Mc" & the Proper case of the rest of the characters in the string -- "Closkey." Maybe I built it wrong, but I think such a thing is possible.

[mojodragon]

I would have to tinker with it for a while, but I'm sure it's possible. Let me play around with Access for a while, and I'll let you know.

[Charlatan]

It has been a while since I played with access but couldn't you do a filter for all instances of "cc" and then find and replace the "cc" with "cC"...

Just a thought.

[CBlend]

Thanks, mojodragon. Hope you are better at this than I am because figuring this out is killing me!

Write your own function - this one should do the job:

Function McStrConv(expr As Variant) As String
Dim ThirdChar As String
McStrConv = StrConv(expr, 3)
If Left(McStrConv, 2) = "Mc" And Len(McStrConv) > 2 Then
    ThirdChar = UCase(Mid(McStrConv, 3, 1))
    McStrConv = "Mc" & ThirdChar & Right(McStrConv, Len(McStrConv) - 3)
End If
End Function

Expression builder should also be able to manage this, but it sucks so badly I try to avoid it.

[CBlend]

Thanks, zen! If I can stay out of meetings long enough to do some actual work tomorrow, I'll give it a shot!