---

MS Access 2007: 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 2007 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2007, I'm trying to write a query that will return only the last name value from a Customer_Name field. The format of the Customer_Name field is:

LastName, FirstName

So, if the Customer_Name 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 Customer_Name field:

Left([Customer_Name],InStr([Customer_Name],",")-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 Customer_Name 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.