"Mc" names formatted with third letter capitalized

[dtencza]

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?

 

[Rick B]

Do a search. Asked and answered often. This is not a function of Access,
but you can do it if you want to invest a great deal of time into
maintaining a table of all these special cases.

In short, make your users enter data in the correct case.

Rick B

 

[Jerry Porter]

You could create your own convert function to use on last names, like:

Function ConvertLast(sLastName As String)
Dim sRet As String
If Left(sLastName, 2) = "mc" Then
sRet = "Mc" & StrConv(Mid(sLastName, 3), vbProperCase)
ElseIf Left(sLastName, 2) = "o'" Then
sRet = "O'" & StrConv(Mid(sLastName, 3), vbProperCase)
Else
sRet = StrConv(sLastName, vbProperCase)
End If

ConvertLast = sRet
End Function

Then use ConvertLast(LastName) in your query. You could add ElseIf
clauses to handle other letter combinations as I did for O' (as in
O'Hare). It seems to me that it would be hard to make this perfect,
since some names can be capitalized in more than one way.

Jerry

 

[fredg]

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?

You'll need to set up a table of exception's for all of the words that
require more than one capital letter, i.e. O'Connor, O'Toole,
MacDaniels, IBM, etc. as well as names/words that should not be
capitalized, i.e. van den Steen, van Beethoven, etc., and those words
whose capitaliztion depends upon context, i.e. ABC and abc, etc.
Include a DLookUp in the code to return a message if the name/word is
included in the table, and give the user the means to override the
StrConv.
Add new names to the table as they are found.

 

[dtencza]

Thanks all.

Does anyone know a way to do this without writing Visual Basic? I am pretty
clueless in that area.

 

[fredg]

Thanks all.

Does anyone know a way to do this without writing Visual Basic? I am pretty
clueless in that area.

Follow these instructions and you should be all set.

Make a new table.
Field: [ID] AutoNumber Indexed No Duplicates
Field: [ExceptName] Text
TableName: tblExceptions

Enter as many known name exceptions as you can.

====
Copy and Paste this function into a new module.
*** Some of the longer lines may be incorrectly wrapped due to your
mail reader.***

Public Function ConvExceptions(StringIn As String) As String

' Will find exceptions to Proper Case capitalization of names.

On Error Resume Next

If DCount("*", "tblExceptions", "[ExceptName] = " & Chr(34) & StringIn
& Chr(34) & "") > 0 Then
Dim intResponse As Integer
Dim strFind As String
strFind = DLookup("[ExceptName]", "tblExceptions", "[ExceptName] =
" & Chr(34) & StringIn & Chr(34) & "")
intResponse = MsgBox(strFind & vbCrLf & " is an exception name." &
vbCrLf & " Accept the above capitalization? Y/N ?", vbYesNo,
"Exception found!")

If intResponse = vbYes Then
ConvExceptions = strFind
Exit Function
End If
End If

ConvExceptions = StrConv(StringIn, 3)

End Function
======

Call it from a Query:
Exp:ConvExceptions([FieldName])
Set the criteria for [FieldName] to
Is Not Null

Be prepared to respond to the message box if an exception is found.

Or use it in a Control's AfterUpdate event to check whwn new names are
added:

If Not IsNull([ThisField]) Then
[ThisField] = ConvExceptions([ThisField])
End If

Add new names to the exceptions table as they occur.

Also remember that there are multiple correct capitalizations of
names, O'Connor and O'connor are both correct, depending upon the
individual preference, and some words can be capitalized or not,
depending upon usage i.e. "The city's main street is Main Street."

 

[Douglas J. Steele]

Be aware that not all Mc names have their third letter capitalized. I know
several Mcdonalds who get quite incensed when their name is misspelled as
McDonald.

 

[Tom Wickerath]

Check out this handy utility:

http://www.infoplan.com.au/splitter/

It can convert names to proper case. However, in Doug's example of McDonald vs. Mcdonald, you
might need to do a manual correction.

Tom
________________________________


Thanks all.

Does anyone know a way to do this without writing Visual Basic? I am pretty
clueless in that area.

 

[dtencza]

If anyone knows how I would do this using the Expression Builder (or knows a
site that might explain how), I'd be really, really appreciative!!

 

[Doug M]

If the last name is in its own field (called say LastName) this expression
seems to work:

IIf(UCase(Left([LastName],2))="MC", Left([LastName],2) &
UCase(Mid([LastName],3,1)) & Right([LastName],Len([LastName])-3),
[LastName])

 

[dtencza]

I played around a bit with the Expression Builder and came up with this if
anyone is interested:

IIf(UCase(Left([LastName],2))="MC",StrConv(Left([LastName],2),3) &
UCase(Mid([LastName],3,1)) &
LCase(Right([LastName],Len([LastName])-3)),StrConv([LastName],3))

Basically, this means if the first two letters of the LastName are "MC",
then join "Mc" and the capital of the third letter and then the rest of the
letters in the name in lowercase after that, otherwise just convert the name
to Proper case.

Hope someone else out there can use it - I sure spent enough time thinking
about it!

 

[Douglas J. Steele]

Realistically, there's no need to use the first UCase statement:
Left([LastName], 2) = "MC" is sufficient, as Access isn't case sensitive.

 

[fredg]

I played around a bit with the Expression Builder and came up with this if
anyone is interested:

IIf(UCase(Left([LastName],2))="MC",StrConv(Left([LastName],2),3) &
UCase(Mid([LastName],3,1)) &
LCase(Right([LastName],Len([LastName])-3)),StrConv([LastName],3))

Basically, this means if the first two letters of the LastName are "MC",
then join "Mc" and the capital of the third letter and then the rest of the
letters in the name in lowercase after that, otherwise just convert the name
to Proper case.

Hope someone else out there can use it - I sure spent enough time thinking
about it!

This is very nice, but how about poor Mr. MacGregor and Ms. van den
Steen, as well as Mr. O'Brien and Mrs. Smith-Jones?
And, what if Mr. Mcdonald takes offense about being called Mr.
McDonald?

Peoples names don't fit into neat categories.