MS Access Query Tip: How to Extract Titles and Last Names

Untitled Document

This question comes from a blog reader in Athens, Greece. While reading the blog post on splitting first and last names, he had a follow-up question:

I have a problem. I would like to know how to isolate title and last name in access. For example there is name, let’s say, Mr. John Brown and I want to isolate and to make it look like Mr. Brown. Could you help?

access query results - title and last name extracted



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


Syntax: Left (string, length)
Returns a specified number of characters from the left side of a string.

Syntax: Right (string, length)
Returns a specified number of characters from the right side of a string.

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, starting from the beginning of the string.

Syntax: InStrRev(«string_to_search», «stringmatch», «start», «compare»)
Allows you to find the position of the first occurrence of a string in another string, starting from the end of the string.


Our sample table has a field (strName) that shows the names of employees.

Extract the Title (ex., Mr.)

Create a query. In the query, create a field and use a combination of Left and InStr functions to extract the title.

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

Here’s a look at Title field in the query design grid.

access query functions to extract title

Extract the Last Name (ex., Brown)

The second field in your query will use a combination of the Mid and InStrRev functions to extract the last name. Note: this combination can also extract the last word in any string.

strLName: Mid([strName],InStrRev([strName],” “)+1)

Here’s a look at the Last Name field in the query design grid.



Join the Title and Last Name (ex., Mr. Brown)

Once you’ve extracted the title and last name, joining them together is simple. Just use the concatenation symbol (&).

strTitleLName: [strTitle] & ” ” & [strLName]

access query concatenate title 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=""> <s> <strike> <strong>