How to extract Middle name in an access form

[ielamrani]

Hi,
I have a full name in text box in a form (Access 2003) like this:

Betty A. Wells

I want to show names (First, Last and Middle) in seperate text boxes.

I was able to get First and last by doing:

=Left([PartName],InStr([PartName]," ")-1)
=Right([PartName],InStr([PartName]," ")-1)

i am not sure how to do Middle name.

Thanks in advance.

 

[xenou]

One strategy would be to continue to work with left and right using nested formulas, maybe (in English) --

To (first) pull everything to the Right of the first Space (should be middle name and last name), and then (second) everything to the left of the First Space from the first result (should be last name now).

=Left(Instr(Right(Instr([PartName]," "))

Perhaps trimming off the spaces:
=TRIM(Left(Instr(Right(Instr([PartName]," ")))

Another more interesting variation might be to get the first and last names (with spaces), then replace these with "nothing", leaving the middle name (also would handle non-existent middle names) - I'm also not thrilled about these periods being in there (do you want a middle name of "A." or "A". I think Access has a Replace function.

The nested formulas could get a few levels deep but shouldn't be beyond you. There's no doubt better solutions available - let us know if you continue to have trouble. Personally I'd want to hide this in a custom function but not sure if you're up to that --

More here (and a function example too)
http://www.tek-tips.com/viewthread.cfm?qid=1434251&page=13

(googled: access split first last middle names)

 

[SydneyGeek]

You will have to watch out for double-work surnames like
Mac Donald
van Jarsfeld
de Aar
etc.

Maybe searching the names for the first space after the 4th character will avoid that -- something like
MID(MID([FullName],4),INSTR(1,MID([FullName],4)," ")+1,1)
should capture the middle initial without the full stop.

Denis

 

[ielamrani]

Thank you all for your help.
I found the answer and I want to share it with you. Please take a look. it has the answers to how to seperate names.

http://support.microsoft.com/kb/209045

Again thanks.

 

[james_lankford]

those will still give you the problems Sydney spoke about

I can't offer a solution, I'm just saying, be careful

 

[ielamrani]

Thanks. I noticed that but it's fine.
I have this name is the list: William C. Van Winkle

It's worked.