Separate First Middle Last names

  • Thread starter Thread starter galin
  • Start date Start date
G

galin

Hi group,

I have a text field containng First Name, Middle Initial
(but not for all names), and Last Name. How do I separate
the field that every name goes to an own field. I have the
following SQL

SELECT Left(Name,InStr(Name," ")-1) AS FirstName,Right
(Name,Len(Name)-InStr(Name," ")) AS LastNames, *
FROM My Table;

which separates thr First Name from the rest. But how do I
have the Middle initial separated from the Last name
(considering the fact that not all names have Middle
Initial)

thanks guys

galin
 
Galin

Insert this into your select query after LastNames:-
,Left([Lastnames],InStr([Lastnames]," ")-1) AS MidName

You can have another column [Firstname]&" "&[Midname]. You can hide columns in the resulting
display, or design a report with only Firstname and Lastname visible.

As I'm sure you realise it's not good practice to have a table with single name field, but
permanently split it into first and last names. This is done with one or more update queries (or
via Excel text to columns).

hth
Andrew L.
 
Hi Galin,

Names are notoriously difficult to parse and you can spend a lot of time
tweaking the code to deal with yet another exception :(

I came across a NameParser class recently which can be referenced in Access,
Excel, Word and VB.Net etc. This thing does the grunt work behind the scenes
and it did a good job on my test table of 250,000 records. Once registered
in Windows and referenced in your project it provides methods and properties
which you can call in your code eg:

?GetFirstName("Rear Admiral Steven Jones") returns "Steven"
?GetMiddle Name("Rev Dr Brian Keith Miller") returns "Keith"
?GetLastName("Monroe Jnr., Tim") returns "Monroe"

I found it on Microsoft's web site at their Office Marketplace site:
http://tinyurl.com/22bdn

Hope this helps!
- Julie
 
Hi Galin,

Names are notoriously difficult to parse and you can spend a lot of time
tweaking the code to deal with yet another exception :(

I came across a NameParser class recently which can be referenced in Access,
Excel, Word and VB.Net etc. This thing does the grunt work behind the scenes
and it did a good job on my test table of 250,000 records. Once registered
in Windows and referenced in your project it provides methods and properties
which you can call in your code eg:

?GetFirstName("Rear Admiral Steven B. Jones") returns "Steven"
?GetMiddle Name("Rev Dr Brian Keith Miller") returns "Keith"
?GetLastName("Monroe Jnr., Chris") returns "Monroe"

I found it on Microsoft's web site at their Office Marketplace site:
http://tinyurl.com/22bdn

Hope this helps!
- Julie
 
Hi Galin,

Names are notoriously difficult to parse and you can spend a lot of time
tweaking the code to deal with yet another exception :(

I came across a NameParser class recently which can be referenced in Access,
Excel, Word and VB.Net etc. This thing does the grunt work behind the scenes
and it did a good job on my test table of 250,000 records. Once registered
in Windows and referenced in your project it provides methods and properties
which you can call in your code eg:

?GetFirstName("Rear Admiral Steven B. Jones") returns "Steven"
?GetMiddle Name("Rev Dr Brian Keith Miller") returns "Keith"
?GetLastName("Monroe Jnr., Chris") returns "Monroe"

I found it on Microsoft's web site at their Office Marketplace site:
http://tinyurl.com/22bdn

Hope this helps!
- Julie
 
Hi Galin,

Names are notoriously difficult to parse and you can spend a lot of time
tweaking the code to deal with yet another exception :(

I came across a NameParser class recently which can be referenced in Access,
Excel, Word and VB.Net etc. This thing does the grunt work behind the scenes
and it did a good job on my test table of 250,000 records. Once registered
in Windows and referenced in your project it provides methods and properties
which you can call in your code eg:

?GetFirstName("Rear Admiral Steven B. Jones") returns "Steven"
?GetMiddle Name("Rev Dr Brian Keith Miller") returns "Keith"
?GetLastName("Monroe Jnr., Chris") returns "Monroe"

I found it on Microsoft's web site at their Office Marketplace site:
http://tinyurl.com/22bdn

Hope this helps!
- Julie
 
Back
Top