---

MS Access 2003: Extract Last Name from a Full Name value in a query

This MSAccess tutorial explains how to extract a last name from a full name value in a query in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, I'm trying to write a query that will return only the last name value from a CustomerName field. The format of the CustomerName field is:

LastName, FirstName

So, if the CustomerName field contains the following value:

Smith, John

How do I retrieve the value "Smith"? (ie: the last name value only)

Answer: This can be done by using the Instr function and Left function as follows:

The following formula will extract the last name value from the CustomerName field:

Left([CustomerName],InStr([CustomerName],",")-1)

This formula finds the first occurrence of a comma using the Instr function. It then uses the Left function to extract the characters from the CustomerName field up to (but not including) the comma.

So when we run this query, we get the following results:

As you can see, Expr1 now contains the Last Name value only.