tech on the net

MS Access: Extract Last Name from a Full Name value in a query in Access 2003/XP/2000/97

See solution in other versions of Access:

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:

Microsoft Access

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:

Microsoft Access

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