[MS Access] Proper case with "Mc" names - Tilted Forum Project Discussion Community

CBlend · 02-12-2005, 09:18 PM

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 · 02-13-2005, 06:34 AM

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 · 02-13-2005, 07:00 AM

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 · 02-13-2005, 11:23 AM

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

02-14-2005, 07:04 AM

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

Code:

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

02-14-2005, 07:08 AM

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

CBlend · 02-16-2005, 05:05 PM

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

02-12-2005, 09:18 PM	#1 (permalink)
CBlend Junkie Location: Classified	[MS Access] Proper case with "Mc" names 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.

02-13-2005, 07:00 AM	#3 (permalink)
Charlatan Getting it. Super Moderator Location: Lion City	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. __________________ "My hands are on fire. Hands are on fire. Ain't got no more time for all you charlatans and liars." - Old Man Luedecke

02-13-2005, 06:34 AM	#2 (permalink)
mojodragon Darth Mojo Location: Right behind you...	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.

02-13-2005, 11:23 AM	#4 (permalink)
CBlend Junkie Location: Classified	Thanks, mojodragon. Hope you are better at this than I am because figuring this out is killing me!

02-14-2005, 07:08 AM	#6 (permalink)
zen_tom Guest	Expression builder should also be able to manage this, but it sucks so badly I try to avoid it.

02-16-2005, 05:05 PM	#7 (permalink)
CBlend Junkie Location: Classified	Thanks, zen! If I can stay out of meetings long enough to do some actual work tomorrow, I'll give it a shot!