Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Normalize Stree Name? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Looking for query code to separate and display the street name and street suffix into two separate colums

    This is a one time conversion of approx 100K records and I would assume some manual cleanup

    Thanks, John

    Input Sample
    ADAMS AVE
    AINSWORTH PKWY
    ALEXANDRA TERR
    ALFRED AVE
    ALLEN ST
    ALPINE
    AMY JENN DR
    ANGOLA PL
    APPLEWOOD ST
    BACKUS RD
    BELLE HEIGHTS
    BENNETT ROAD
    BRIARIDGE LN
    CARDINAL CT
    CARRIE PL
    EDEN EVANS RD
    EDEN TOWN LINE
    GEMINI
    N MAIN ST
    S CREEK RD
    SOUTHWESTERN
    STURGEON PT RD

    Output

    <table border=1> <td>ADAMS</td> <td>AVE </td><td>AINSWORTH</td><td>PKWY</td><td>ALEXANDRA</td><td>TERR</td><td>ALFRED</td><td>AVE</td><td>ALLEN </td><td> ST</td><td>ALPINE</td><td> </td><td>AMY JENN</td><td> DR</td><td>ANGOLA</td><td> PL</td><td>APPLEWOOD</td><td> ST</td><td>BACKUS</td><td> RD</td><td>BELLE HEIGHTS</td><td> </td><td>BENNETT</td><td> ROAD</td><td>BRIARIDGE</td><td> LN</td><td>CARDINAL</td><td> CT</td><td>CARRIE</td><td> PL</td><td>EDEN EVANS</td><td> RD</td><td>EDEN TOWN LINE</td><td> </td><td>GEMINI</td><td> </td><td>N MAIN</td><td> ST</td><td>S CREEK</td><td> RD</td><td>SOUTHWESTERN</td><td> </td><td>STURGEON PT</td><td> RD</td></table>

  2. #2
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Normalize Stree Name? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Try the following functions. GetFirstWord takes your input string & a delimiter string & returns your input string up to the first occurrence of your delimiter string. GetLastWord is similar but returns your input string after the first occurrence of your delimiter string. In your example the delimiter string would be " ".


    Function GetFirstWord(strIn As String, chrDelimit As String) As String
    ' Parameters : strIn - string to search
    ' chrDelimit - character delimiter
    ' if delimiter is not found whole string is returned
    Dim strTmp As String
    Dim intPos As Integer
    Dim strFirstName
    Dim strLastName

    strTmp = Trim$(strIn)
    intPos = InStr(strTmp, chrDelimit)

    If intPos = 0 Then
    GetFirstWord = strTmp
    Else
    GetFirstWord = left$(strTmp, intPos - 1)
    strLastName = Mid(strTmp, intPos + 1)
    End If

    End Function

    Function GetLastWord(strIn As String, chrDelimit As String) As String
    ' Parameters : strIn - string to search
    ' chrDelimit - character delimiter
    ' if delimiter is not found null is returned
    Dim strTmp As String
    Dim intPos As Integer
    Dim strFirstName
    Dim strLastName

    strTmp = Trim$(strIn)
    intPos = InStr(strTmp, chrDelimit)

    If intPos = 0 Then
    GetLastWord = ""
    Else
    GetLastWord = Mid(strTmp, intPos + Len(chrDelimit))
    End If

    End Function

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Normalize Stree Name? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    See the attached demo. I created an auxiliary table to hold the suffixes such as AVE and RD, you can add records to this table as needed.
    There are three queries to get the end result. Since you're using Access 2000, I added a function InStrReverse. In Access 2002 and higher, you'd use the much faster built-in function InStrRev.

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Normalize Stree Name? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hi Ian

    Thanks for the code, it caught about 95% of my street names, I will put this in my code to keep database for future refrence

    John

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Normalize Stree Name? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Great demo Hans, definitely a keeper.

    For others that might have a need, I included the official United States Postal Service table of:

    1. Primary Street Suffix Names
    2. Commonly Used Street Suffix or Abbreviations
    3. Standard Suffix Abbreviations

    This will allow me to normalize the street name with Commonly Used Street Suffix or Abbreviations, then update them with Standard Suffix Abbreviations.

    Thanks, John

Posting Permissions

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