Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to separate one field into two? (2000)

    We have a field that contains an address in the form of house number and street. Is there a way to write a query that will separate the house number (which always comes before a space and then the street name) from the street name and put them into separate fields? The house numbers may be anywhere from one to four characters. The street names obviously are irregular as well, and sometimes contain multiple words. The two fields I want the data in already exist in the table. I'm hoping there is some kind of update query that will separate the data (otherwise it's cut-and-paste for 4329 records!)

    Thanks,
    -cynthia

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: How to separate one field into two? (2000)

    If you can guarantee that the street number (1 to 4 characters) is the first part of the field and what follows the space is in fact the street name then you should be able to use the InStr function, eg.
    Suppose that the field name is currently Address, then the following should suffice:
    Left(Address, Instr(1, Address, " ", 2)-1) as StreetNumber, Mid(Address, Instr(1, Address, " ", 2)+1) as Streetname

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: How to separate one field into two? (2000)

    Here is a slight variation of Patt's suggestion.

    It also uses the Instr function, but uses the right function instead of the Mid function.

    the street number can be of any length, but must not contain a space.
    I just noticed that the closing ) got left out of my picture!
    Attached Images Attached Images
    Regards
    John



  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to separate one field into two? (2000)

    Thanks to both of you! I will try this tomorrow.
    I had an idea that a query could perhaps do the trick - but beyond that I was pretty well baffled.
    Thank you,
    -cynthia

Posting Permissions

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