Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Location
    New York
    Posts
    47

    Unanswered: Splitting a name into First and Last

    I am trying to strainghten out several databases created by someone else and combine them into one back end. Some have names as first and last, some the full name, some a last, first or last,first. Yes, it's a mess. I can take the various tables out to excel and split the names but I would rather do it right in Access. In Excel I would do it with left, right, find and len but there is no find or len in Access, can anyone help?

    Thanks
    Terri
    Terri Gavin

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Access has Left, Right and Len. I haven't used Find, but it appears that the Access equivalent is InStr.
    Paul

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Sounds like fun (especially if there is also a middle name.) There is a len expression in Access. I'm assuming your breaking these apart into a LastName, FirstName, a MiddleName (and possibly a Prefix and Suffix field)? I hope there aren't thousands of names.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Why bother... seriously... if you can do the job easily with Excel, then use Excel! No need to complicate matters.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Dec 2003
    Location
    New York
    Posts
    47
    Paul, thank you so much. It worked perfectly.

    To the other 2 gentleman who responded...
    Because I have 14 tables and counting that needed names split for one or more fields per table. It is a lot less work to write a formula in the append query than to export, split with the Excel list command, import and then append!
    Terri Gavin

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok cool... I thought you were just making things harder for yourself :P

    Thanks for the feedback
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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