Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Smile Unanswered: Seperating Street Name and Street Number from Address Field

    Hi,

    I have trouble figuring out how to seperate an Address field in a query. The Address column contains street number and street name and I have to seperate them into different columns (street number column and street name column) so that I can differentiate records which have the same first name and last name by their address. I was told that I need to write scripts on the query grid under Field row but I could not figure it out.

    Any help would be appreciated, thanks!

  2. #2
    Join Date
    Jul 2004
    Posts
    125
    Use the Instr(), Left$(), Mid$(), and the Right$() functions to manipulate street addresses. I am just curious as to why would you want to do that?

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would suggest looking into getting some third party software. Determining which part of the address is the house number or the street name can be very complicated. There are companies that spend alot of time and money proving to the U.S. Post Office that they can accurately determine this. It's called CASS certification. The other benefit to buying their software is they typically come with a way to determine if there are duplicates within a list, which is what I am assuming you are trying to do.

    I use a couple of products from these companies that are somewhat reasonable, Accuzip and The Software Company. I would reccommend the ActiveX control from The Software Company. It will split addresses for you, but you do need to know how to program to be able to use it.

  4. #4
    Join Date
    Aug 2004
    Posts
    2
    Thanks ineuw and DCKunkle.

    The reason I wanted to do this because I am working with PDI Database and there are hundred of thousands of voters in one city. Imagine if I have to work with a few cities, I would have a bunch of people who has the same last name and first name. In order to match them more precisely, I have to match their addresses as well. Sometimes, I have some records with the same last name, first name and street name so I have to match them with street number.

  5. #5
    Join Date
    Jul 2004
    Posts
    125
    I agree with DCKunkle's comment, and my job IS processing address databases containing 100,000's or records on a daily basis for the US and Canada Postal systems. M4rl1n may consider having the lists processed by companies specializing in this field, (ours included ). The cost of the software is too prohibitive for temporary purchase. In addition to CASS coding, there is also NCOA (National Change Of Address) processing by companies licenced by the USPS and Canada Post. You (M4rl1n) should also consider the fact that street address segments are more than just numbers.

Posting Permissions

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