How to take data from one field and split it into two fields?

[t.a.smith]

I'm redoing a database and need to make the Name field, which now contains
the First Name AND Last Name in one field. I need to split the names up so
they are each in their own separate fields ie: First Name and a 2nd field for
Last Name. Is there an easy way to do this?

 

[GM Leonric]

I'm redoing a database and need to make the Name field, which now contains
the First Name AND Last Name in one field. I need to split the names up so
they are each in their own separate fields ie: First Name and a 2nd fieldfor
Last Name.  Is there an easy way to do this?

Yup as long as the names are pretty standard and you don't have people
like John Von De Grot (where Von De Grot is the last name).

FirstName: Left(Trim([Field]),InStr(Trim([Field])," "))
LastName: Mid(Trim([Field]),InStrRev(Trim([Field])," ")+1)

Keven

 

[Tom van Stiphout]

On Tue, 13 Oct 2009 19:12:17 -0700 (PDT), GM Leonric

There is indeed no way to do this without making some assumptions and
living with the consequences. Two-word names are simple, but
three-word ones are not:
Mary Jane Smith
Hillary Rodham Clinton
You have to decide whether the first one word or first two words
constitute the firstname, and live with the fact you'll sometimes get
it wrong.

I recommend documenting all your rules, checking them for consistency,
and then implementing them. Personally I would rather write the rules
in VBA then in SQL:
select GetFirstName(FullName) from SomeTable

public function GetFirstName(byval FullName as string) as string

-Tom.
Microsoft Access MVP

I'm redoing a database and need to make the Name field, which now contains
the First Name AND Last Name in one field. I need to split the names up so
they are each in their own separate fields ie: First Name and a 2nd field for
Last Name.  Is there an easy way to do this?

Yup as long as the names are pretty standard and you don't have people
like John Von De Grot (where Von De Grot is the last name).

FirstName: Left(Trim([Field]),InStr(Trim([Field])," "))
LastName: Mid(Trim([Field]),InStrRev(Trim([Field])," ")+1)

Keven

 

[Jeff Boyce]

As you've seen, you'll come up with a routine for parsing the FullName field
into 2 (or more), then apply USB technology for the best result (USB=using
someone's brain).

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[t.a.smith]

THANK YOU SO MUCH! I used this in an update query and it worked perfectly!!
This will save me a lot of time and sleepless nights. Again, it was very
helpful.

I'm redoing a database and need to make the Name field, which now contains
the First Name AND Last Name in one field. I need to split the names up so
they are each in their own separate fields ie: First Name and a 2nd field for
Last Name. Is there an easy way to do this?

Yup as long as the names are pretty standard and you don't have people
like John Von De Grot (where Von De Grot is the last name).

FirstName: Left(Trim([Field]),InStr(Trim([Field])," "))
LastName: Mid(Trim([Field]),InStrRev(Trim([Field])," ")+1)

Keven