How to un-concatenate a field with full name

[Patricia]

I have a filed called "Contact" that has the full first and last name - is
there a way to break this field into 2 fields - "first name", 'last name" ???
How do you find where to split the field?

Thanks

 

[smk23]

Take a look at "InStr" function in vba

 

[Jeff Boyce]

Patricia

GOOD LUCK!

There's a very good reason (several, but...) good database design uses "one
fact, one field". Stuffing two name-types (first, last) into one field
means you have to figure out how to break them apart.

If you are 100% absolutely certain that your Contact field contains ONLY
FIRSTNAME SPACE LASTNAME, you can use a query that looks for a SPACE and
takes everything to the left of it, and everything to the right of it. Your
other response mentions using the InStr() function (i.e., where's the
SPACE).

If your Contact field contains any variations on this, you'll need to employ
USB (using someone's brain).

GOOD LUCK!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Klatuu]

AS Jeff pointed out, names should be in seperate fields. Parsing names is
second only in frustration to parsing addesses. There are too many
variations in names to be able to create a routine that will get it right
100% of the time.

 

[Duane Hookom]

You can use these string functions:
Left() function to return X number of characters at the beginning of a
string.

Instr() can find the occurance of one string in another (hint, you can
find a space).

Mid() is used to return a section of character from within a string.