Microsoft Access A to Z: Breaking the first and last name out of a single field

This short screencast will examine the common relational database problem of entering the first, middle, and last names into a single field which makes it difficult to sort by last name or select only part of a name for a letter, email, or report. This screencast will show how to use an Access query to separate the pieces of a string by using calculated fields to separate out the parts of a field using Access built-in functions such as InStr(), Iif(), Len(), Left(), and Right()
All of my KZbins can be found at: learncs.w3spac...

[@greenhoodie]

This is super helpful, one of the reports I get comes with first and last name combined in the one column(annoying). This video has given me ideas on how to tackle it......Thank you🙂

[@lfriedrichsen]

You are very thoughtful to respond like this, and are also very welcome!

[@greenhoodie]

@@lfriedrichsen I appreciate the effort you go to in making these videos! Big 👍

[@beatrixbujak1571]

Thank you

[@lfriedrichsen]

Thank you for taking the time to write. All of my KZbins are organized at learncs.w3spaces.com. Take care.

[@4450520]

Thank you, I got the Idea much appericiated.

[@lfriedrichsen]

Great. This is a very common data management problem.

[@ssdusd]

Great 👍 👌

[@lfriedrichsen]

You're welcome! You can find all of my KZbins organized at learncs.w3spaces.com/

[@garyrideumup5443]

How would you use the Trim formula with this?

[@lfriedrichsen]

It all depends on the data. If it has leading and/or trailing spaces in the full name, then the Trim function would be very helpful as it returns a string without leading and trailing spaces. Also, if there was more than one space between the first and last names, you could further scrub the first and last names using the Trim function.

[@mr.k5691]

Kindly how do you find names with a specific number of letters please?

[@lfriedrichsen]

In a query, you need to calculate the length of the name using the len function and then use query criteria to select only the records with whatever length you desire. An example of the calculated field named length to determine the length of the LastName field values using the Len function would be: length: Len([LastName])