• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 91
  • Last Modified:

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?
3 Solutions
ste5anSenior 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)," " )))
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 FyeCommented:
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.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now