Capitalisation of names

  • Thread starter Thread starter PayeDoc
  • Start date Start date
P

PayeDoc

Hello All

Where can I find some code that will correctly capitalise names?

I currently have:

Private Sub employee_name_AfterUpdate()
Me!employee_name= StrConv(Me!employee_name, 3)
End Sub

but as everyong knows this doesn't deal properly with the Mc's, Mac's,
hyphenated names, etc.
I have searched the web and this newsgroup but cannot find anything - other
than various opinions that input masks are to be avoided! As this must be
such a universal requirement I am sure the code must be 'out there', but my
VBA isn't up to it (yet!) and I hate trying to reinvent wheels anyway.


Hope someone can help

Many thanks
Leslie Isaacs
 
I suspect there's nothing out there because it's definitely not a trivial
problem!

For example, I know both McDonalds and Mcdonalds. How could software know
which is correct when?
 
Hello Douglas

OK: not so simple then!!

I could get 95% right if I could just capitalise:

the first letter
the first letter after any hyphens
the first letter after any spaces
the first letter after any "Mc" or "Mac" strings

How could I adapt my current code ...
Me!employee_name= StrConv(Me!employee_name, 3)
.... to deal with the 4 cases above?

The user would need to be able to correct the remaining 5% (including the
Mcdonalds etc.) manually - so I guess I would need to move the eventcode
from being on AfterUpdate to on Enter?

Hope you can help!
Thanks again
Leslie Isaacs
 
Something like the following untested air-code:

Function CapitalizeName(NameIn As String) As String
Dim lngPos As Long
Dim strNameOut As String

strNameOut = StrConv(Me!employee_name, 3)

' Capitalize letters after spaces
lngPos = InStr(strNameOut, " ")
Do While lngPos > 0
Mid(strNameOut, lngPos + 1, 1) = _
UCase(Mid(strNameOut, lngPos + 1, 1))
lngPost = InStr(lngPos + 1, strNameOut, " ")
Loop

' Capitalize letters after hyphens
lngPos = InStr(strNameOut, "-")
Do While lngPos > 0
Mid(strNameOut, lngPos + 1, 1) = _
UCase(Mid(strNameOut, lngPos + 1, 1))
lngPost = InStr(lngPos + 1, strNameOut, "-")
Loop

' Capitalize the letter after Mc
If Left(strNameOut, 2) = "Mc" Then
Mid(strNameOut, 3, 1) = UCase(Mid(strNameOut, 3, 1)
Else
lngPos = InStr(strNameOut, " Mc")
If lngPos > 0 Then
Mid(strNameOut, lngPos + 3, 1) = _
UCase(Mid(strNameOut, lngPos + 3, 1))
End Loop
End If

' Capitalize letters after Mac
If Left(strNameOut, 3) = "Mac" Then
Mid(strNameOut, 4, 1) = UCase(Mid(strNameOut, 4, 1)
Else
lngPos = InStr(strNameOut, " Mac")
If lngPos > 0 Then
Mid(strNameOut, lngPos + 4, 1) = _
UCase(Mid(strNameOut, lngPos + 4, 1))
End Loop
End If

CapitalizeName = strNameOut

End Function
 
PayeDoc said:
Hello Douglas

OK: not so simple then!!

I could get 95% right if I could just capitalise:

the first letter
the first letter after any hyphens
the first letter after any spaces
the first letter after any "Mc" or "Mac" strings

How could I adapt my current code ...
Me!employee_name= StrConv(Me!employee_name, 3)
... to deal with the 4 cases above?

The user would need to be able to correct the remaining 5% (including
the Mcdonalds etc.) manually - so I guess I would need to move the
eventcode from being on AfterUpdate to on Enter?

Hope you can help!
Thanks again
Leslie Isaacs
Click to expand...
 
PayeDoc said:
Hello Douglas

OK: not so simple then!!

I could get 95% right if I could just capitalise:

the first letter
the first letter after any hyphens
the first letter after any spaces
the first letter after any "Mc" or "Mac" strings

How could I adapt my current code ...
Me!employee_name= StrConv(Me!employee_name, 3)
... to deal with the 4 cases above?
Click to expand...
Probably with a lot of if statements.
X = Me!employee_name ' to lazy to type it a lot.
Pos =Instr ( X, "-" )
If Pos then
X = left(X,Pos) & strconv(mid(X,Pos+1),3) "VERY UNTESTED but close.
End If

Pos = Instr(X, "Mc")
If Pos then
X = left(X,Pos) & strconv(mid(X,Pos+2),3) "VERY UNTESTED
End If

Etc.

Why don't you have a first and last name field?

My experience has been that the data entry people will tend to get it right
and that if a mistake is made the named person will correct it if important
to them.
 
Hello Douglas

That's great!
With a couple of typos corrected your function did exactly what I needed -
many thanks.

Les
 
Hello

Many thanks for this.
In fact I have used Douglas' simpler function, which was enough for what I
needed.

Thanks again
Les
 
Hello Mike

Many thanks for your reply.
In fact I have used Douglas' function, which was enough for what I needed.

Thanks again
Les
 
Back
Top