Parsing text in access 2007

Posted on 2007-10-21
Last Modified: 2008-05-19
I have imported some datas to access 2007 from a text file. One of the field is an artist full name with 2 or 3 words included (Elton John) or (Evelyn Peggy March).
How can I parse this "full name" field to 2 separated fields (First Name and Last Name) like (John, Elton) or (March, Evelyn Peggy) ?
Please note that I'm a novice and I cannot maybe understand when you explain only the function (without how and where I must enter).
Question by:cisilay
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Hello cisilay,

    This is actually a more complex question than I think you realize.  Have a look at this
    current question for a sense of the difficulties behind what, at first glance, sounds
    like such a simple thing:



    Author Comment

    These kind of formulas are very complicated for me.
    OK, if I want only parse 2 words from one "full name" field to 2 separated fields (First Name) (Last Name), without suffix or without middle name, like only from (Elton John) to (Elton) and (John), is it easy  and if yes how ?
    Please don't forget to answer for a novice user.
    LVL 92

    Accepted Solution


    If all of the following are true:

    1) There is *always* a fname and *always* a lname
    2) Both fname and lname are always one word each
    3) The fname is always listed first
    4) The two are always separated by a space

    then the following query would separate them:

    SELECT Left(FullName, InStr(1, FullName, " ") - 1) AS FName, Mid(FullName, InStr(1, FullName, " ") + 1) AS LName
    FROM YourTable



    Author Comment

    OK thanks, it works well.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    The canonical version of this article is on my web site here: A companion presentation is available here:
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now