Parsing name into correct fields

[Guest]

I have two functions that separate a "first name" field, called FName,
consisting of either just a first name (Melanie), a first name and middle
initial (Melanie J), or a first name and middle name (Melanie Jean), into two
separate first name and middle name columns. The two functions are listed
here:

First Name: Left([FName],InStr([FName]," ")-1)
Middle Name: Mid([FName],InStr(1,[FName]," ")+1,Len([FName])-InStr([FName],"
"))

The problem I'm having is that if there is no middle initial or name
present, the first name function returns a #Error, and the real first name
shows up as the middle name. How can I adjust the two expressions above to
correctly show a first name without a middle name in the first name column?
For testing purposes, if there is no middle name, we can assign the initial
"X". Thanks for any help!

Melanie

 

[[MVP] S.Clark]

You could do this in two queries instead of one. Those with & those
without.

Assuming that no first names contain a space, you could react to that. You
may be able to wrap it into one big expression, but I prefer the two query
method.

 

[Guest]

This will do what you want --
First Name: IIf(InStr([FName]," ")=0,[fnAME],Left([FName],InStr([FName],"
")-1))
Middle Name: IIf(InStr([FName]," ")=0,"",Mid([FName],InStr(1,[FName],"
")+1,Len([FName])-InStr([FName]," ")))

What are you going to do for those double first names?
Billy Jean King
Billy Bob Thornton
Jim Bob Hatcher
Mary Ann Marks

 

[Duncan Bachen]

I have two functions that separate a "first name" field, called FName,
consisting of either just a first name (Melanie), a first name and middle
initial (Melanie J), or a first name and middle name (Melanie Jean), into two
separate first name and middle name columns. The two functions are listed
here:

First Name: Left([FName],InStr([FName]," ")-1)
Middle Name: Mid([FName],InStr(1,[FName]," ")+1,Len([FName])-InStr([FName],"
"))

The problem I'm having is that if there is no middle initial or name
present, the first name function returns a #Error, and the real first name
shows up as the middle name. How can I adjust the two expressions above to
correctly show a first name without a middle name in the first name column?
For testing purposes, if there is no middle name, we can assign the initial
"X". Thanks for any help!

Melanie

You can wrap the entire thing in an IIf function to first see if a space
exists (assuming thats your criteria for a middle name.

Middle Name: IIf(InStr(1,[Fname]," "), Mid([FName],InStr(1,[FName],"
")+1,Len([FName])-InStr([FName]," ")),"X")

That says, if there is a space in the FName field, then do your previous
function for extracting the name, if not, return the value of "X"

You can also do the same thing if the FName is empty

First Name: IIf(Len(Nz([FName],"")) = 0, "Missing Name",
Left([FName],InStr([FName]," ")-1))