Access - Split Field with First Name Middle Name/Initial - Middle Returned if there is one

I have tried the following:

Right([First Name],Len([First Name])-InStrRev([First Name]," "))

In a First Name field that has First Name and may contain a middle name or middle initial.

If there is a Middle Entity, this is returned rather than the First Name. If there is no Middle Entity, the First Name is returned.

The preference is to only have the First Name returned.

Thank you
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.

Evan CutlerVolunteer Chief Information OfficerCommented:
split the query.

Assuming Last, First M.

Update <table> set First_Name = Left([Person_Name],InStr([Person_Name],",")-1), Middle_Name = Right([Person_Name],1), Last_Name = Left([Person_Name],InStr([Person_Name],",")-1)
0
exp vgAuthor Commented:
Let me clarify - I am splitting a field that only contains First Name and maybe a Middle Entity.

The above posted includes a Last Name component.
0
Evan CutlerVolunteer Chief Information OfficerCommented:
ok.  If you are First M,

then kill the Last_name:

Update <table> set First_Name = Left([strName],InStr([strName],” “)-1), Middle_Name = Right([strName],Len([strName])-InStr([strName],” “))

How about that?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

exp vgAuthor Commented:
I tried this, and for an error:

Left([First Name],InStr([First Name]," ")-1), Middle_Name = Right([First Name],Len([First Name])-InStr([First Name]," "))

Let me please clarify that I do not want the query to remove the middle entity at all, Just the first name.

Thank you..
0
exp vgAuthor Commented:
The error is invalid comma or missed quotation marks for:

Left([First Name],InStr([First Name],” “)-1), Middle_Name = Right(First Name],Len([strName])-InStr([First Name],” “))
0
Evan CutlerVolunteer Chief Information OfficerCommented:
ok.  try this:

Update <table> set <field> =
   (Select
      case when <source_field> NOT LIKE '% %' then <Source Field>
      else Right([First Name],Len([First Name])-InStr([First Name]," "))
      from <source table>
  )

ok, try this.  In this case, you are seeing if the field has a space, which implies first and middle names.  If it does then get middle name, otherwise, the assumption is that it only has a first name, so copy.
0
exp vgAuthor Commented:
Will the split statement work for this?

This seems so much more complicated than it should.

I tried what you offered (thank you)

 (Select case when <First Name> NOT LIKE '% %' then <First Name>
      else Right([First Name],Len([First Name])-InStr([First Name]," "))
      from <ENR> )

And received an error
0
Evan CutlerVolunteer Chief Information OfficerCommented:
sorry....

forgot the end.

 (Select case when <First Name> NOT LIKE '% %' then <First Name>
      else Right([First Name],Len([First Name])-InStr([First Name]," "))
      from <ENR> end)
0
exp vgAuthor Commented:
Bummer still not working. I truly thank you for your patience.

I tried your original version - but have also tried replacing the < with [

(Select case when <First Name> NOT LIKE '% %' then <First Name>
      else Right([First Name],Len([First Name])-InStr([First Name]," "))
      from <ENR> end)

I still get a syntax error.

Thank you.
0
Evan CutlerVolunteer Chief Information OfficerCommented:
send me the exact query.
0
exp vgAuthor Commented:
This is the only code that I have:

(Select case when [First Name] NOT LIKE '% %' then [First Name] else Right([First Name],Len([First Name])-InStr([First Name]," ")) from [ENR] end)

Is this what you are asking for?

Thank you.
0
Evan CutlerVolunteer Chief Information OfficerCommented:
ok.  You have two fields.

Your source field that has a first or first/mid name, and your target field which has the first name OR Middle Name,

right?
0
exp vgAuthor Commented:
No - just one field [First Name]

Some examples of what is included in this field are:

JOHN
JOHN W
JOHN WILLIAMS

The end result that is preferred is:

JOHN

Hope this helps.

Thank you
0
Evan CutlerVolunteer Chief Information OfficerCommented:
ok, and you trying to cleanse the field to just the first name or NULL?
0
exp vgAuthor Commented:
Let me please clarify.

The field will have a name in it regardless (never null), but it should never be the middle entity.

I have tried other formulas - but what happens is it removes the first name and only returns the middle entity.

Every cell in this column has a First Name as well - and this is all that is required.

I am also still not certain why the Split statement I tried did not work. I would think it would for this.

Thank you
0
Evan CutlerVolunteer Chief Information OfficerCommented:
ok.  I think I understand.

update <table> set [First Name] = Left([First Name],InStr([First Name]," ")-1)

You have a right call on it.  Let's remove it.
0
exp vgAuthor Commented:
I was so hoping this would work - but no.

As my earlier outcome, if the cell has a first name, the outcome is null. If there is a middle entity, only the middle entity is returned.
0
IrogSintaCommented:
The Split function does not work inside a query.  It can only used in VBA code within a module.  Are you trying to remove the Middle Name if it's included in the First Name field?  If so, try this:

FirstName: IIf([Product Name] Like "* *",Left([Product Name],InStr([Product Name]," ")-1),[Product Name])

Ron
0
exp vgAuthor Commented:
Thank you IrogSinta - but I get an error that the formatting is not correct. I did modify yours to try and align the parantheses, but still no luck:

FirstName: IIf(([First Name] Like "* *",Left([First Name],InStr([First Name]," ")-1),[First Name])

Thank you
0
Evan CutlerVolunteer Chief Information OfficerCommented:
Try this:
FirstName: IIf(([First Name] Like "* *"),Left([First Name],InStr([First Name]," ")-1),[First Name])
0
exp vgAuthor Commented:
Bummer - still an error. If it means anything - the cursor blinks on the set of parantheses after the IIF.I tried removing one of them and this statement still did not work.

Thank you.
0
exp vgAuthor Commented:
This is basically the same as using

Text to Columns in Excel - where the field is First Name - but may or may not contain a Middle Entity - with a space delimiter.

Thank you.
0
IrogSintaCommented:
You had an extra parenthesis in the beginning.  Copy this:
FirstName: IIf([First Name] Like "* *",Left([First Name],InStr([First Name]," ")-1),[First Name])

Ron
0

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
exp vgAuthor Commented:
Finally!

Thank you so much - I know this has been effort by both of you.
0
exp vgAuthor Commented:
Thank you very much.
0
Evan CutlerVolunteer Chief Information OfficerCommented:
IrogSigna is the yoda in this one.  Had he not spoke up, this would still be going on.
Thanks
0
exp vgAuthor Commented:
I emphasize - thank you both. I know you both put effort into this.
0
IrogSintaCommented:
Welcome, you are.
;-)
0
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.

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.