1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2000 - Dividing data into several columns

Discussion in 'Business Applications' started by Erm, Jun 17, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    I have a column that contains the full name of a client, eg. Mr B Cooney and I want to divide this into three columns (Title, Firstname and Surname). Can anyone help?
     
  2. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    Just posted this reply to move the message back to the top of the list, as there have only been 6 views. Well, any ideas people?
     
  3. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Are they ALWAYS like that, Erm?
    Or might there be also Mr B E Cooney?
     
  4. ptvGuy

    ptvGuy

    Joined:
    Apr 20, 2004
    Messages:
    78
    The easiest way is to divide Title, First Name, and Last Name into seperate fields in your table. That allows you to break them up and work with them seperately in forms, queries, and reports. I won't deal with it any other way. However, if you must keep it all in a single field, you're going to have to write code to seperate the pieces and truncate them into your seperate columns. You'll probably have it seperate pieces by looking for the spaces between the words which is fine as long as no one ever makes an error entering data. For instance, if I've entered the following three names into a single field like this:
    Mr. John Doe
    Mrs.Jane Doe
    Stephanie Doe
    Then the code is going to break them up like this:
    TITLE: Mr. FIRST NAME: John LAST NAME: Doe
    TITLE: Mrs.Jane FIRSTNAME: Doe LAST NAME: #error#
    TITLE: Stephanie FIRST NAME: Doe LAST NAME: #error#
    That would require writing a lot of error handling routines in the code that truncates it or a lot of code for the initial validation of the data as it's entered. It's up to you, but I wouldn't want to code that.
     
  5. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
  6. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    All names are in this format
    Lightfoot, Mrs L
    Dixon, Mrs. C. E. M.

    So it probably isn't possible to divide this into title, firstname, surname. What code would I need to divide it into everything before the comma and everything after the comma...can you give me a pointer?
     
  7. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    So you only want TWO columns now?
     
  8. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
  9. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    Excellent...sorry to be such a pain in the butt! I'll try it out and let you know how it goes!
     
  10. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    You're not a pain in the butt! :)

    Sorry to you...sometimes I just say it without thinking how it might come across in text. And sometimes, posters think stating EXACTLY what you want is being picky. But code requires it. I've got my fingers crossed.

    Also, so that you know, we're building a knowledgebase of code over there, so it's really fine to have several methods...we'll use them!
     
  11. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    I didn't take offence, I just realised I hadn't thought about the problem enough before I posted the thread! Bit of a blonde moment I always say! I do quite like the old VBA...I'll have to join in on some discussions over there!
     
  12. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Well...we'd love to have ya.
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/239891

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice