Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Splitting up the text in a field (97)

    I discovered the left, mid and part commands for queries, but of course what I want to do is too complicated for that.

    I got some information from another organization, and they don't split the first and surnames up in their database. I do in mine. Is there an easy way to run a query or something to split these two pieces of data using the space between them as the spot for making the split? Unfortunately, I either have to do this manually, or I need the answer pretty quickly, as we're going to be running a mail merge starting this afternoon.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Splitting up the text in a field (97)

    The Instr function should do the trick for most names - it will search for a space in a text string, and in combination with Mid, Left and Right you can decompose names - except for the de, van, Mc and other such names, but you may also have issues with some records having three names, prefixes, suffixes, etc.

    If it isn't a ridiculous number of records, I often do it with Excel. Pull the data in, and have it break the name into columns using the Data/Text to Columns. You will want to provide several empty columns to do that, but you can then quickly spot the goofy records and put things right in columns, and then import back in Access. Hope this helps.
    Wendell

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting up the text in a field (97)

    Thanks, Wendell, that was exactly what I was looking for.

    Since I was exporting the data to excel anyway, I was able to really quickly do the work, and then imported again at my leisure.

Posting Permissions

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