In article <ac7c3567-932a-4db2-90a4-708c75cd61e9@
79g2000hsk.googlegroups.com>, jamensonespind...@hotmail.com says...
>
>
>
>
>
Quote:
Originally Posted by
On 4 jul, 20:01, fredg <fgutk...@example.invalidwrote:
Quote:
Originally Posted by
On Fri, 4 Jul 2008 15:48:50 -0700 (PDT), Jamenson wrote:
Hi everyone!
|
|
>
Quote:
Originally Posted by
Quote:
Originally Posted by
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.
|
|
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Any help appreciated.
|
|
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Thank you!
|
|
>
Quote:
Originally Posted by
Quote:
Originally Posted by
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.
|
|
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Be aware, also, that sometimes the same name can be capitalized
differently. McDaniels and Mcdaniels are both correct.
|
|
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Of course the best method is correct data entry. <g>
|
|
>
Quote:
Originally Posted by
Quote:
Originally Posted by
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
|
|
>
Quote:
Originally Posted by
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.
|
>
Quote:
Originally Posted by
Best regards.
|
>
Quote:
Originally Posted by
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- Ocultar texto entre aspas -
>
- Mostrar texto entre aspas -
