String manipulation
in [Help]
|
From: Jamenson on 4 Jul 2008 18:48 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!
From: fredg on 4 Jul 2008 19:01 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
From: Jamenson on 5 Jul 2008 14:07 On 4 jul, 20:01, fredg <fgutk...(a)example.invalid> wrote: > 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.
From: Salad on 6 Jul 2008 15:27 Jamenson wrote: > On 4 jul, 20:01, fredg <fgutk...(a)example.invalid> wrote: > >>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. This is what I've done in the past. When a person enters data into a name field, within a form, I will it compare to proper case. If the two don't match, I convert it to proper case. After that, I leave it alone. If a person "fixes" the proper case it was because he/she wanted/needed to or is a bonehead. I create a variable in the declarations section of a form's module Dim blnName As Boolean In the OnCurrent event I'll set the value of the variable. If the namefield has a value, it's set to true blnName = (IsNull(Me.YourNameField)) In the AfterUpdate event of NameField I have code similar to If not blnName then 'it was an empty name field. 3 = vbProperCase If Me.YourNameField <> StrConv(Me.YourNameField,3) then Me.YourNameField = StrConv(Me.YourNameField,3) msgbox "The name field was propercased." blnName = True Endif Endif If the person get's the message, they can adjust without warning afterwards. Lambada http://www.youtube.com/watch?v=5AfTl5Vg73A
From: Michael Gramelspacher on 6 Jul 2008 20:43
In article <ac7c3567-932a-4db2-90a4-708c75cd61e9@ 79g2000hsk.googlegroups.com>, jamensonespindula(a)hotmail.com says... > On 4 jul, 20:01, fredg <fgutk...(a)example.invalid> wrote: > > 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 |