tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment
Share this page:

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.