Results 1 to 5 of 5
  1. #1
    tld is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    2

    Need to remove the suite #, aka "#", or just number, bldg, etc from address line

    Have 1 field that includes the house number, street. This same line includes the suite number, bldg number, etc.



    Tried several queriers with iff statements, but I think it will be easier to do in a module.

    Need to remove all information after the street (road, drive, blvd, parkway, etc) and place in a separte field. Cannot parse due to additional information can start with Suite or Ste or # or bldg or just a number.

    Examples:

    • 1234 Anywhere street
    • 1234 Anywhere Street ste 100
    • 1234 Broadway Juniper Road ste 100
    • 1234 Anywhere Street #100
    • 1234 Broadway Juniper Road #100
    • 1234 Anywhere Street 100
    • 1234 Anywhere Street bldg 1
    • 1234 Anywhere Street bldg 1 sute 100
    • etc.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I suggest you show samples from your real data.
    Also, iff is actually iif.

  3. #3
    tld is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    2
    Thank you for responding. I know it is iif, just typed it wrong. I am not sure why it makes a difference on the actual data but here are a few examples out of the 100,000+ rows that I have
    1655 N MOUNT VERNON AVE #B1
    2015 W OLYMPIC BLVD # 101
    2208 W 7TH ST
    5901 W OLYMPIC BLVD #504
    814 E BROADWAY STE 1
    11539 HAWTHORNE BLVD 6E
    11633 HAWTHORNE BLVD #410
    16133 VENTURA BLVD #360
    18370 BURBANK BLVD #204
    18370 BURBANK BLVD #701
    20911 EARL ST STE 440
    21311 MADRONA AVE #100B
    21311 MADRONA AVE #100C
    4314 W SLAUSON AVE 1 3 FL
    520 N PROSPECT AVE #200
    6221 WILSHIRE BLVD #416
    9025 WILSHIRE BLVD #210
    11539 HAWTHORNE BLVD FL 6
    7345 MEDICAL CENTER DR STE 600
    1245 WILSHIRE BLVD #707
    125 N JACKSON AVE STE 206
    14651 S BASCOM AVE #250
    150 N JACKSON AVE STE 105
    1565 MABURY RD STE D
    1661 BURDETTE DR STE F
    1715 LUNDY AVE #108-116
    175 N JACKSON AVE STE 208
    200 JOSE FIGUERES AVE 325
    200 JOSE FIGUERES AVE455

  4. #4
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I suggest you will need a function to inspect the string on a word by word basis to determine where your required address finishes. Something like


    Code:
    Function AddressPart(addrstr as string) as string
    dim str() as string
    dim I as integer
    dim result as string
    
    str=split(addrstr," ")
    result=""
    for I=0 to ubound(str)
        select case str(I)
           case "BLVD","ST","AVE"....extend for all options for last word of address
               result=result & str(I) 'add final word of the address required and exit function
               addresspart=trim(result)
               exit function
           case else
               result=result & " " & str(I) 'add this part of the address and move on
        end select
    next i
    addresspart="*** " & addrstr 'if the end part of address not found, add 3 stars (or other characters) so they can be identified by filtering.
    end function

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you're going to have all kinds of trouble with this, just look at your last entry you have AVE455 instead of AVE 455

    What I would do were I you is do this in stages because parsing a string for an unknown value is really going to be difficult. I say unknown value because STREET could be ST, STREET, ST., STRT, STREEET with all kinds of misspellings/problems. *ANY* free form text field is going to be extremely hard to parse.

    So, create 2 new fields on your table
    ConvAddress ConvUnit

    Run an update query on your table, include ConvAddress and ConvUnit only
    in the UPDATE TO section for CONVADDRESS put in

    left([AddressField], instr([AddressField], "ST") + 2)

    in the CONVUNIT field put in

    iif(instr([AddressField], "ST") + 2 = len([AddressField]), null, Right([AddressField], len([AddressField]) - instr([AddressField], "ST") + 2))

    CHECK THE PARENTHENTICAL PAIRS I DIDN'T

    now, this will fail for something like

    2345 STEAMBOAT ST

    because the first ST the code encounters is not actually the street portion. Like I said this is going to be a time consuming project because there's no reliable pattern.


    You might have more luck peeling out the addresses that have the # signs first then possible the STE fields

    those would simply be
    left([FieldName], instrrev([FieldName], "STE")
    and
    right([FieldName], len([FieldName]) - instrrev([FieldName], "STE"))

    you may have to play around with the spacing but those might do the bulk of your processing.

    Another thing you might consider is just build a simple form and break up your string into something a bit wider spaced (each character goes in it's own box) then put a command button between each character so you could click the button between the characters to indicate where you wanted to break it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-27-2015, 10:03 PM
  2. Replies: 7
    Last Post: 09-20-2014, 06:48 PM
  3. Replies: 4
    Last Post: 07-12-2014, 02:02 PM
  4. Replies: 1
    Last Post: 12-10-2013, 12:59 AM
  5. Replies: 5
    Last Post: 12-06-2010, 10:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums