Separating First & Last Names Into Two Fields 1

[ibanezGuitars]

I've got an old Metafile database (don't ask) in which the original author stored client's first and last names together in the same field (Name) separated by a space.

Smith Joe
Jones Mary

I can handle the Mid() function to place the substrings of the first and last names into two separate fields but what function would I use to identify in which position the space between the names occurs?

Thanks For Your Time,

Dave

 

[mkov]

intLoc = instr(1, yourfieldnamehere, " ")
strFirst = mid(yourfieldnamehere,1, intloc - 1)
strLast = mid(yourfieldnamehere, intloc + 1, maxlengthoffield)

This is what I do in your type of situation.

This is off the top of my head and my need tweaked a little.

Mark

 

[emmi59]

But what if there are names like

John Paul Jefferson Smith III jr.

What would you consider to be the first and the last name and what about other name parts like suffixes?

 

[mkov]

I have never had to deal with name fields that contained something other than the first name and last name.

Off the top of my head, not sure how I would do it. If I get some free time today, I will see if I can come up with something.

 

[emmi59]

Don´t waste your time. There is no programmatic solution for dumb computers to tell the name parts apart. Just imagine that if there was only one field for names, everybody could type in whatever they want:

Tommy
Aunt Mary
John Doe
J. R. Ewing
John Foster Dulles
Kennedy, John F.
qo2975293847,099
<script>format("C")</script>

or whatever.

The guess to use the first blank as a separator is probably the best you can ever make...

 

[ibanezGuitars]

Thank you for the posts - I don't think 3 part names were in widespread usage 25 years ago. (Boy do I feel old.) We'll be matching either Case #'s or SSN #'s so the names are really a secondary validation check.

Also, these Metafile tables were created back in the days when you were fortunate to have a double set of 5-1/4" floppy drives let alone (gulp) a 10 meg hard drive! Used to be common practice to piggyback fields to save on size and space restrictions - just like using 2 positions to indicate the year when storing a date. Just think of how many hours of OT we got because of that one alone.

Thanks again,

Dave