Create two calculated fields in a table based on one field entered

I have a table with a field named "Name".  If I enter John Doe in the field I want two calculated fields in the table to populate.  One being "FirstName" and the result of the calculation would be "John".  The other calculated field would be "LastName" and the result would be "Doe".

I've tried these expressions in the FirstName calculated field but get an error.  (all characters up to the first space)

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

But then also, what would be the proper expression for the LastName?

But I also think I need to trim any leading spaces before and after "John Doe" in the event one was typed, correct?
Who is Participating?
ste5anConnect With a Mentor Senior DeveloperCommented:
To keep this short: Don't try it. There are for example double names without hyphen. Names which are entered in the wrong order. You will get for sure wrong names.

I would create two new normal columns and update them once. Then the users need to clean up the values. Depending on the context, this could be feáture in your input UI, but not in the table itself.

To parse use: Mid([FullName],1,InStr([FullName]," ")-1)  and Mid([FullName],InStr([FullName]," ")+1). But you need to add an IIF to test whether a space is in your column to avoid errors.
try for a field name t:
trim(left(trim(t), instr(trim(t)," ")-1))
trim(right(trim(t),len(trim(t))-instr(trim(t)," " )))
PatHartmanConnect With a Mentor Commented:
Once you use a reserved word or a name with embedded spaces or special characters you ALWAYS have to enclose the offending name in square brackets.

FirstName:Left([Name],Instr([Name]," ")-1)
LastName:Mid([Name], Instr([Name], " ") +1)
Dale FyeConnect With a Mentor Commented:
I agree with Ste5an, your best bet is to go with a single field for Firstname and another field for LastName on your form.  Why do you think most web applications ask for these two pieces of data separately, it is because it is difficult to separate them properly, because you never know what someone will enter when given a Name field.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.