Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    Clarksville, Tn
    Posts
    1

    Question Unanswered: Parse a reversed Street Address String

    Using Access or VB we need to parse a street address field from Propaddress field to Streetname, Streetnumber, StreetType that has fields in a reverse format. The fields are mixed, some have Ft. Cambell, St. George, etc as the name, and all are space delimited. i.e
    FT CAMPBELL BLVD 2861
    Bedford Way
    HWY 48 4761
    101 Airborne Div. Pkwy 2201
    Some will have street numbers and some have not, some have a prefix like Ft. Camble but most not.
    Any sql expressions that could take and parse this or a VB routine would be the only way we can get them in seperate fields.

  2. #2
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    18

    Re: Parse a reversed Street Address String

    Originally posted by mapx
    Using Access or VB we need to parse a street address field from Propaddress field to Streetname, Streetnumber, StreetType that has fields in a reverse format. The fields are mixed, some have Ft. Cambell, St. George, etc as the name, and all are space delimited. i.e
    FT CAMPBELL BLVD 2861
    Bedford Way
    HWY 48 4761
    101 Airborne Div. Pkwy 2201
    Some will have street numbers and some have not, some have a prefix like Ft. Camble but most not.
    Any sql expressions that could take and parse this or a VB routine would be the only way we can get them in seperate fields.


    mhhm?

    I think it can be done with strings


    watch topic at:

    http://www.mvps.org/access/strings/str0001.htm



    Firstname: Right$([Name],Len([Name])- InStr(1,[Name]," ")-1)

    LastName: Left$([Name],InStr(1,[Name]," ")-1)

    Note: Using similar logic and through the use of Instr, Mid, Left, Right and other string functions, you can extract any part of a given string.


    Using the following logic, you can either run an Update query or assign the values through a DAO operation in code.




    I hope its of any use to u

Posting Permissions

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