«

»

MS Access Query Tip: How to Split Names to Extract First & Last Names

Untitled Document

 

Here’s the scenario. You have data in a table that contains full names, and you want to be able to split the field so that you have first and last names in separate fields. Unlike Excel, Access doesn’t have a simple way to do this.

In order to split the data in a field, you will need to use a combination of the following Access functions:

  1. Left
    Syntax: Left (string, length)
    Returns a specified number of characters from the left side of a string.
  2. InStr
    Syntax: InStr([start, ]string1, string2[, compare])
    Allows you to search for a character or string within a string, and returns a number telling you the first occurence of one string within another.
  3. Right
    Syntax: Right (string, length)
    Returns a specified number of characters from the right side of a string.
  4. Len
    Syntax: Len(string)
    Returns a length of a string.

In our sample table, we have a field that shows the names of employees.

Access 2010 - List of Employees

Extract the First Name (Access Query Tip)

In your query, create a field and use a combination of the Left and InStr functions to extract the First name.

Left([strName],InStr([strName],” “)-1)

Here’s a screen shot of the function in the Query Design Grid.

Access 2010 - Function to extract first name

Here’s a screen shot of the result.

Access 2010 - Function to extract first name

Extract the Last Name (Access Query Tip)

Create a field in your query design grid and use a combination of the Right, Len and InStr functions to extract the Last name.

Right([strName],Len([strName])-InStr([strName],” “))

Here’s a screen shot of the function in the Query Design Grid.

Access 2010 - Function to extract last name

Here’s a screen shot of the result.

Access 2010 - Function to extract last name

 

Need more Access tips? Subscribe to our email newsletter so that you never miss another one.

About the author.

Jackie Kiadii has 20 years of Access database development experience, and teaches Basic through Advanced Access courses, including an in-depth 4-day Access immersion course.

Access Database Training in Atlanta, GA and Online

Need more MS Access help? Here are your options.

Atlanta Computer Training on Facebook Atlanta Computer Training - LinkedIn Software Tips on Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>