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).
See solution in other versions of Access:
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.
Advertisements