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?
Answer:
In order to split the data in a field, you will need to use a combination of the following Access functions:
Left
Syntax: Left (string, length)
Returns a specified number of characters from the left side of a string.
Mid
Syntax: Right (string, length)
Returns a specified number of characters from the right side of a string.
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, starting from the beginning of the string.
InStrRev
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.
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]
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.
- Access Training in Atlanta, GA and Online
- Subscribe to our email newsletter.
- Need onsite group training? Our courses are affordable and customized to your needs. Available for groups of 4 or more students.
