Parsing Names

[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

 

[Ofer Cohen]

Try using InStrRev to get the space from the right

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

 

[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

 

[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

Try using InStrRev to get the space from the right

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

--
Good Luck
BS"D

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

 

[Alan B. Densky]

Michel,

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

Thanks again,
Alan

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

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

 

[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
..

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

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

 

[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

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
.

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

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