In article <ac7c3567-932a-4db2-90a4-708c75cd61e9@
79g2000hsk.googlegroups.com>,
ja***************@hotmail.com says...
On 4 jul, 20:01, fredg <fgutk...@example.invalidwrote:
On Fri, 4 Jul 2008 15:48:50 -0700 (PDT), Jamenson wrote:
Hi everyone!
I want to convert strings like "LEONARDO DI CAPRIO" to "Leonardo di
Caprio". The function StrConv converts to "Leonardo Di Caprio" and we
think it is innapropriated.
Any help appreciated.
Thank you!
You'll have to create a User Defined Function using StrConv() and a
look-up table to find names with these different kinds of
capitalizations, i.e. McDonald, O'Brien, Smith-Jones, van den Steen,
etc.,
or .....
manually search and change these relatively few oddball
miss-capitalizations.
Be aware, also, that sometimes the same name can be capitalized
differently. McDaniels and Mcdaniels are both correct.
Of course the best method is correct data entry. <g>
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Thank you Fred for your help, but, unfortunately, I am not a clever
boy in Microsoft Access. Even I am not a professional IT or
professional programmer, or such like that. I have designed a database
to supply the needs of a Small Office. When we started using the
database, I have noticed that people was typing customer names in an
innadequate way: capitalized. So, if you could, explicitly, show me
the code, it could be better for me understant and implement it.
Best regards.
Jamenson Ferreira Espindula
Recife - Pernambuco - Brazil.
I think you will have a rough time converting this string, because you
are not separating the first name and the last name. There should be a
table column for first name and another column for last name. Often
there
are also columns for middle name, title and suffix.
Dr. | Jameson | Ferreira | Espindua | M.D.
Private Sub txt_last_name_AfterUpdate()
If Not IsNull(Me.txt_last_name) Then
Me.txt_last_name = ProperCase(Me.txt_last_name)
End If
End Sub
Note that this function is not my code. You should be able to google for
the referenced message.
'===
Function ProperCase(ByVal strName As String) As String
' Function mConvSN is from Michael Rochler,
' microsoft.publip.access.formscoding, 3 Jul 2000
Dim strOriginal As String
Dim intIsSame As Integer
strOriginal = strName ' save original name
strName = mConvSN(strName) ' convert to proper case
ProperCase = strOriginal ' this if the default value
' next 2 lines for testing only
'intIsSame = StrComp(strName, strOriginal, 0)
'Debug.Print strName, strOriginal, intIsSame
If StrComp(strName, strOriginal, 0) Then
If MsgBox("Change " & strOriginal & " to " & strName & "?", _
vbYesNo + vbQuestion, "Entry changed!") = vbYes Then
ProperCase = strName
End If
End If
End Function
'-----------------------------------------------------------
Public Function mConvSN(ByVal varTxt As Variant) As Variant
'-----------------------------------------------------------
' this is part of ProperCase
On Error GoTo Err_mConvSN
Static astrPrefix(14) As String 'I changed this number
Dim strPatt As String
Dim iNdx As Integer
Dim iPos As Integer
Dim bOK As Integer
' initialize
mConvSN = vbNullString
varTxt = Trim(varTxt)
If mChkIsNothing(varTxt) Then Exit Function
' initialize array
InitArray_mConvSN:
astrPrefix(0) = "D' "
astrPrefix(1) = "D'"
astrPrefix(2) = "Da "
astrPrefix(3) = "De La "
astrPrefix(4) = "De "
astrPrefix(5) = "Du "
astrPrefix(6) = "La "
astrPrefix(7) = "Le "
astrPrefix(8) = "Van Den "
astrPrefix(9) = "Van Der "
astrPrefix(10) = "Van "
astrPrefix(11) = "Von Den "
astrPrefix(12) = "Von Der "
astrPrefix(13) = "Von "
astrPrefix(14) = "Di "
GoSub Proper_mConvSN
GoSub Foreign_mConvSN
GoSub Scottish_mConvSN
GoSub Hyphenated_mConvSN
GoSub Apostrophe_mConvSN
mConvSN = varTxt
On Error GoTo 0
Exit Function 'mConvSN
'-------------------------------------------------
'Doc_mConvSN: 'Documentation; not actually called.
'PURPOSE:
'Convert surname to proper case, but also handle correctly standard
'surname prefixes such as "von der", "de la", etc. Calling routine
'should take measures to check whether spelling is unchanged, but
'capitalisation has changed, in which case user is not happy with
'suggested surname prefix and has overridden it (perhaps because it
'should be Anglicised).
'IN:
' varTxt - The surname to be automatically converted.
'OUT:
' - Function returns converted surname.
'EXAMPLES:
' mConvSN ("roCHLER") returns "Rochler"
' mConvSN (" roCHLER ") returns "Rochler"
' mConvSN("VON DER BORCH") returns "von der Borch"
' mConvSN("FOTHERINGTON-THOMAS") returns "Fotherington-Thomas"
' mConvSN ("MACDONALD") returns "Macdonald"
' mConvSN("MCINTYRE") returns "McIntyre"
' mConvSN ("o'conNOR") returns "O'Connor"
' mConvSN ("de LA forge") returns "de la Forge"
'Doc_mConvSN
'------------
Err_mConvSN:
MsgBox "Sorry, an error has occurred." & vbCrLf & _
"Error number: " & Err.Number & vbCrLf & "Error: " & _
Err.Description, vbCritical & vbOKOnly, "Error"
Resume Next
'Err_mConvSN
'------------------
Apostrophe_mConvSN:
'Surnames like O'Connell.
If varTxt Like "*'*" Then
iPos = InStr(2, varTxt, "'", 1)
If iPos 0 Then varTxt = Left(varTxt, iPos) & _
UCase(Mid(varTxt, iPos + 1, 1)) & _
Right(varTxt, Len(varTxt) - iPos - 1)
End If
Return 'Apostrophe_mConvSN
'---------------
Foreign_mConvSN:
'Find and convert foreign prefixes to lower case.
For iNdx = 0 To 14
strPatt = astrPrefix(iNdx) & "*"
If varTxt Like strPatt Then
varTxt = LCase(astrPrefix(iNdx)) & _
Right(varTxt, Len(varTxt) - Len(astrPrefix(iNdx)))
Exit For
End If
Next iNdx
Return 'Foreign_mConvSN:
'-------------------
Hyphenated_mConvSN:
'Hyphenated surnames like Bindon-Howell
' or Fotherington-Thomas.
If varTxt Like "*-*" Then
iPos = InStr(2, varTxt, "-", 1)
If iPos 0 Then varTxt = Left(varTxt, iPos) & _
UCase(Mid(varTxt, iPos + 1, 1)) & _
Right(varTxt, Len(varTxt) - iPos - 1)
End If
Return 'Hyphenated_mConvSN
'-----------------
Proper_mConvSN:
'Convert every word to a l/c word beginning with initial
'u/c character.
varTxt = StrConv(varTxt, vbProperCase)
Return 'Proper_mConvSN:
'-----------------
Scottish_mConvSN:
'Scottish: Leave Macdonald alone, but adjust Mc* names like McKay.
If varTxt Like "Mc*" Then varTxt = "Mc" & _
UCase(Mid(varTxt, 3, 1)) & Right(varTxt, Len(varTxt) - 3)
Return 'Scottish_mConvSN
End Function 'mConvSN
'---------------------------------------------------------
Function mChkIsNothing(ByVal varVal As Variant) As Integer
'---------------------------------------------------------
' this is part of properCase
On Error Resume Next
mChkIsNothing = False
Select Case VarType(varVal)
Case vbEmpty, vbNull
mChkIsNothing = True
Case vbString
If Len(varVal) = 0 Then mChkIsNothing = True
Case Else
End Select
End Function 'mChkIsNothing
