Results 1 to 7 of 7

Thread: Text Clean-up

  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: Text Clean-up

    I am submitting a file to a name matching service and have a few MSAccess questions.

    First: How best to remove comma's and periods from the address? Is there an MSAccess query or command to do this?

    Second: Some of the last names have a Jr. or a 'III' which i need to remove. Any recommendations on how best to strip that out as well will be appreciated.

    Third. I have a zip+4 which is '00000-0000' which i need to convert to '000000000'.

    I appreciate any help you can offer. Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    First:
    You know...there probably is but I can't think of one now. This can however be done through VBA code by way of creating a function to carry out the task. For example:

    Code:
    Public Function RemoveAllCommasPeriodsFromString (Strg As String) As String
    	'This function will remove all Commas and Periods from the supplied string.
    	Dim a$, i As Integer
    	On Error Resume Next
    	Strg = Trim (Strg) 
    	For i = 1 to Len(Strg)
    		a$ = Mid$(Strg, i, 1)
    		If a$ = , Or a$ = "." then a$ = 
    		RemoveAllCommasPeriodsFromString = RemoveAllCommasPeriodsFromString & a$
    	Next I
    End Function

    USAGE:

    Me.myAddressTextBoxName = RemoveAllCommasPeriodsFromString(Me.myAddressTextBoxName)
    __________________________________________________ ________


    Second:
    Another function would be required to carry out this task. Perhaps something like:

    Code:
    Public Function RemoveNameTitles (Strg As String) As String
    	'This function will remove the listed name titles from the supplied string.
    	Strg = Trim (Strg) 
    	If LCase(Right$(Strg, 4)) = " jr." Then Strg = Left$(Strg,  Len(Strg) - 4)
    	If LCase(Right$(Strg, 3)) = " jr" Then Strg = Left$(Strg,  Len(Strg) - 3)
    	If LCase(Right$(Strg, 4)) = " sr." Then Strg = Left$(Strg,  Len(Strg) - 4)
    	If LCase(Right$(Strg, 3)) = " sr" Then Strg = Left$(Strg,  Len(Strg) - 3)
    	If LCase(Right$(Strg, 4)) = " esq." Then Strg = Left$(Strg,  Len(Strg) - 4)
    	If LCase(Right$(Strg, 3)) = " esq" Then Strg = Left$(Strg,  Len(Strg) - 3)
    	If LCase(Right$(Strg, 4)) = " phd." Then Strg = Left$(Strg,  Len(Strg) - 4)
    	If LCase(Right$(Strg, 3)) = " phd" Then Strg = Left$(Strg,  Len(Strg) - 3)
    	If LCase(Left$(Strg, 4)) = "dr. " Then Strg = Mid$(Strg,  5, Len(Strg))
    	If LCase(Left$(Strg, 3)) = "dr " Then Strg = Mid$(Strg, 4, Len(Strg))
    	If UCase(Right$(Strg, 4)) = " III" Then Strg = Left$(Strg, Len(Strg) - 4)
    	If UCase(Righ$(Strg, 3)) = " II" Then Strg = Left$(Strg,  Len(Strg,) - 3)
    	If UCase(Righ$(Strg, 3)) = " IV" Then Strg = Left$(Strg,  Len(Strg,) - 3)
    	If UCase(Righ$(Strg, 2)) = " V" Then Strg = Left$(Strg,  Len(Strg,) - 2)
    	RemoveNameTitles = Strg
    End Function

    USAGE:

    Me.myNameTextBox = RemoveNameTitles(Me.Me.myNameTextBox)

    __________________________________________________ _______________

    Third:
    Use the Format function.



  3. #3
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Are you familiar at all with functions like InStr, Mid, Len and also IIF and joinging fields with & " " &

    I clean up phone numbers on name listings we buy so they can be checked for duplicates. Some numbers will have - or ( [ or_ }and spaces.

    Basically, you create calulated fields in a query that use functions like Mid and these fields pull of parts of the field with problem entries.

    Say you had phone numbers that were 1234-2345, 234-789, (089) 123-4567 the different functions like Mid put different sections of the number (or text) that you want in to the different calculated fields

    Your example of '00000-0000' which i need to convert to '000000000' would finish with '00000 in one field and 0000' would be another field and a third field joins them.

    I just entered your '00000-0000' into my phone fixer query and the following what 3 calculated fields do. The field created field name is not relevant is jut what I have used for different reasons

    Exp2ab: InStr([CL Ph Home],"-")

    The field gives a result of 7. The 7 is the number of characters where the - occurs

    TestExpab: IIf([Exp2ab]>0,Mid([CL Ph Home],(b][Exp2ab]+1[/b]),100),0)

    The above gave a result of 0000' Note that it references the previous field of Exp2ab. The [Exp2ab]+1 is of course 8

    Then the next field is

    TestExp2ab: IIf([Exp2ab]>0,Mid([CL Ph Home],1,([Exp2ab]-1)),0)

    This gives '00000

    This field joined them

    TestExp3ab: IIf([Exp2ab]>0,[TestExp2ab] & [TestExpab],0)

    so its result was '000000000'

    In the above example it would not have mattered whether you had

    0-0000000000 or 0000000000000-00

    If the inverted comma had to be separated then that would need more.

    If you have entries like 000-000-0000 you do the above but make a SetValue macro action that copies the result back into the original field, or a copy of the original field which what you might use.

    If you like I will pull a couple of parts out of my data base and attach them. It will handle any keyboard entry that I don't already have setup as you enter the character in a field. For example I don't have the thing set up for an * but if I enter an asterisk in another field then it will knock those out.

    You would need to alter what I have to some degree as it is set up to convert the phone numbers in Australia to their 8 numbers plus to number area code.

    Thus it will change, if a Sydney number, 9 345-1234 or 9345-1234 or (02) 9 1235678 or [02] 9123 4567 to 10 straight digits like 0293451234

    If I had )02)912 34567 then I would enter ) in the other field

    Mike

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    Here an other function

    Code:
    Function FindAndReplace(ByVal strInString As String, _
            strFindString As String, _
            strReplaceString As String) As String
    Dim intPtr As Integer
        If Len(strFindString) > 0 Then  'catch if try to find empty string
            Do
                intPtr = InStr(strInString, strFindString)
                If intPtr > 0 Then
                    FindAndReplace = FindAndReplace & Left(strInString, intPtr - 1) & _
                                            strReplaceString
                        strInString = Mid(strInString, intPtr + Len(strFindString))
                End If
            Loop While intPtr > 0
        End If
        FindAndReplace = FindAndReplace & strInString
    End Function
    can't remeber where i found it but it works for me

    NewText = FindAndReplace(oldtext,"-"," ")
    or
    NewText = FindAndReplace(oldtext,"jr"," ")
    Last edited by myle; 06-21-04 at 00:41.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    The Replace function will solve all three of your requirements.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Jun 2004
    Posts
    57

    Thank You

    Thanks for the help.
    Quick follow-up question. I understand the VBA logic. I'm not sure how to link it to my MSAccess table. The table name is PPOdata and the field is Street1Txt. Can you help me understand how to put that into the VBA loop? Thankyou.

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    Quote Originally Posted by r123456
    The Replace function will solve all three of your requirements.
    That's if you access now what the replace function is.

    it's only been in 2000 and up that microsoft has upgraded the vb bit
    of access there are still user who have the old version.
    Last edited by myle; 06-21-04 at 06:44.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •