Parsing Names

A

Alan B. Densky

I have a names field that has inconsistent data in it. Sometimes it has a
FirstName and a LastName, and sometimes it also has a middle initial with or
without a period after it: FirstName MI LastName.

I'm currently parsing the field into FirstName and LastName. However, when
there is a middle initial that gets thrown into the last name field and it
really screws things up when I lookup a person based on last name and the
field starts with the middle initial.

Fst: Trim(Left([CustomerName],InStr([CustomerName]," ")))

Lst: Trim(Mid([CustomerName],InStr([CustomerName]," ")+1))

Can someone help me with another function that will straighten out this
mess?

Thanks,
Alan
 
O

Ofer Cohen

Try using InStrRev to get the space from the right

Lst: Trim(Mid([CustomerName],InStrRev([CustomerName]," ")+1))
 
M

Michel Walsh

Probably slow...


SELECT Split(fieldName, " ")(0) As FirstName,
Split(fieldName, " ")(UBound(Split(fieldName, " "))) As LastName

FROM yourTable



and only with Access 2002 or later.

Vanderghast, Access MVP
 
A

Alan B. Densky

Ofer,

Thanks, that works great. Is there any to parse out the middle initial or
the middle initial and the period if it exists?

Alan


Ofer Cohen said:
Try using InStrRev to get the space from the right

Lst: Trim(Mid([CustomerName],InStrRev([CustomerName]," ")+1))

--
Good Luck
BS"D


Alan B. Densky said:
I have a names field that has inconsistent data in it. Sometimes it has a
FirstName and a LastName, and sometimes it also has a middle initial with or
without a period after it: FirstName MI LastName.

I'm currently parsing the field into FirstName and LastName. However, when
there is a middle initial that gets thrown into the last name field and it
really screws things up when I lookup a person based on last name and the
field starts with the middle initial.

Fst: Trim(Left([CustomerName],InStr([CustomerName]," ")))

Lst: Trim(Mid([CustomerName],InStr([CustomerName]," ")+1))

Can someone help me with another function that will straighten out this
mess?

Thanks,
Alan
 
A

Alan B. Densky

Michel,

Thanks, but I'm using Access 2000. However, the previous poster's answer did
work for me.

Thanks again,
Alan


Michel Walsh said:
Probably slow...


SELECT Split(fieldName, " ")(0) As FirstName,
Split(fieldName, " ")(UBound(Split(fieldName, " "))) As LastName

FROM yourTable



and only with Access 2002 or later.

Vanderghast, Access MVP


Alan B. Densky said:
I have a names field that has inconsistent data in it. Sometimes it has a
FirstName and a LastName, and sometimes it also has a middle initial with
or
without a period after it: FirstName MI LastName.

I'm currently parsing the field into FirstName and LastName. However, when
there is a middle initial that gets thrown into the last name field and it
really screws things up when I lookup a person based on last name and the
field starts with the middle initial.

Fst: Trim(Left([CustomerName],InStr([CustomerName]," ")))

Lst: Trim(Mid([CustomerName],InStr([CustomerName]," ")+1))

Can someone help me with another function that will straighten out this
mess?

Thanks,
Alan
 
J

John Spencer

Sir,

I think that you cannot use the Split function in a query. I get undefined
function error with Access 2003 SP1. I guess you could write a custom
function to use the Split function and return one value from the array.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Michel Walsh said:
Probably slow...


SELECT Split(fieldName, " ")(0) As FirstName,
Split(fieldName, " ")(UBound(Split(fieldName, " "))) As LastName

FROM yourTable



and only with Access 2002 or later.

Vanderghast, Access MVP


Alan B. Densky said:
I have a names field that has inconsistent data in it. Sometimes it has a
FirstName and a LastName, and sometimes it also has a middle initial with
or
without a period after it: FirstName MI LastName.

I'm currently parsing the field into FirstName and LastName. However,
when
there is a middle initial that gets thrown into the last name field and
it
really screws things up when I lookup a person based on last name and the
field starts with the middle initial.

Fst: Trim(Left([CustomerName],InStr([CustomerName]," ")))

Lst: Trim(Mid([CustomerName],InStr([CustomerName]," ")+1))

Can someone help me with another function that will straighten out this
mess?

Thanks,
Alan
 
M

Michel Walsh

You are right, Split does return an array and Jet-SQL evaluation service
does not seem to like the indices syntax:


? eval("Array(1, 2, 3)(1)")
' error

while

? Array(1, 2, 3)(1)
1



John Spencer said:
Sir,

I think that you cannot use the Split function in a query. I get
undefined function error with Access 2003 SP1. I guess you could write a
custom function to use the Split function and return one value from the
array.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Michel Walsh said:
Probably slow...


SELECT Split(fieldName, " ")(0) As FirstName,
Split(fieldName, " ")(UBound(Split(fieldName, " "))) As LastName

FROM yourTable



and only with Access 2002 or later.

Vanderghast, Access MVP


Alan B. Densky said:
I have a names field that has inconsistent data in it. Sometimes it has a
FirstName and a LastName, and sometimes it also has a middle initial
with or
without a period after it: FirstName MI LastName.

I'm currently parsing the field into FirstName and LastName. However,
when
there is a middle initial that gets thrown into the last name field and
it
really screws things up when I lookup a person based on last name and
the
field starts with the middle initial.

Fst: Trim(Left([CustomerName],InStr([CustomerName]," ")))

Lst: Trim(Mid([CustomerName],InStr([CustomerName]," ")+1))

Can someone help me with another function that will straighten out this
mess?

Thanks,
Alan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top