Access - split first name from middle name (or initial)

I have tried to use:

Left([First Name],InStr([First Name],",")-1)

to obtain only the first name from the field "First Name" without any middle name (or initial). I create a new field row in the design view of a table, and indicate it to be calculated.

Please offer the most efficient manner to do this.
exp vgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Well, the easiest way to get everything to the left of the first space in a string is:

Split([First Name], " ")(0)

The split function requires a character or characters to use to separate out all of the segments of a string.  For example if [Full Name] contained "John Paul Jones" then

Split([Full Name], " ")(0) returns "John"
Split([Full Name], " ")(1) returns "Paul"
Split([Full Name], " ")(2) returns "Jones"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hamed NasrRetired IT ProfessionalCommented:
Your code looks fine.

Although not recommended to use calculated fields in tables, you may use your expression in an Update Query.

Update tbl Set newField = Left([First Name],InStr([First Name],",")-1)

Open in new window

exp vgAuthor Commented:
Thank you
Dale FyeOwner, Developing Solutions LLCCommented:
You're welcome!
Hamed NasrRetired IT ProfessionalCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.