1st name Last name

N

Nancy

the first name, middle initial, last name are all stored in a single field.
I only want the 1st 5 characters of the first name ... does anyone know how
do I parse this out ???

data in field : ---- what I want:
Jon L. Jones --- Jon
Lawrence Smith --- Lawre

Thanks,
Nancy
 
R

Rick B

I would highly recommend that you change your data structure. You should
store each part of the name in a separate field. What if you want to sort
by last name? What if you want to address a letter with the first name and
then last name? What if you want to print "Dear:" then the first name in
your letters?

Lots of reasons to do this.

I'm sure you'll get a more detailed answer, but you need to grab the first
five characters Left([NameField],5)

Then, you need to use instring and left to grab only up to the first space
(if any). There are previous posts out there that you can find to do this.
It uses INSTR and subtracts one postion.

A couple of issues though. What if you have J R Ewing? Do you only want
to find "J"? Or maybe it was enterd as J. R. Ewing?
 
J

John Nurick

I would highly recommend that you change your data structure. You should
store each part of the name in a separate field. What if you want to sort
by last name? What if you want to address a letter with the first name and
then last name? What if you want to print "Dear:" then the first name in
your letters?

Lots of reasons to do this.
Click to expand...

There may also be good reasons not to. A significant proportion of the
world's population have names that don't readily parse into the
traditional American pattern "first name" "middle name" "family name".
 
N

Nancy

Excellant - exactly what I needed THANK YOU :)

Nancy

Ofer Cohen said:
Try

FirstName: Left(Left([FieldName],Instr([FieldName]," ")-1),5)

--
Good Luck
BS"D


Nancy said:
the first name, middle initial, last name are all stored in a single field.
I only want the 1st 5 characters of the first name ... does anyone know how
do I parse this out ???

data in field : ---- what I want:
Jon L. Jones --- Jon
Lawrence Smith --- Lawre

Thanks,
Nancy
Click to expand...
Click to expand...
 
N

Nancy

Unfortunately I have no control over the data structure. A file was provided
for me so that I could do some analysis ... and they never make it easy :(

Thanks though for the advice

Nancy

Rick B said:
I would highly recommend that you change your data structure. You should
store each part of the name in a separate field. What if you want to sort
by last name? What if you want to address a letter with the first name and
then last name? What if you want to print "Dear:" then the first name in
your letters?

Lots of reasons to do this.

I'm sure you'll get a more detailed answer, but you need to grab the first
five characters Left([NameField],5)

Then, you need to use instring and left to grab only up to the first space
(if any). There are previous posts out there that you can find to do this.
It uses INSTR and subtracts one postion.

A couple of issues though. What if you have J R Ewing? Do you only want
to find "J"? Or maybe it was enterd as J. R. Ewing?

--
Rick B



Nancy said:
the first name, middle initial, last name are all stored in a single
field.
I only want the 1st 5 characters of the first name ... does anyone know
how
do I parse this out ???

data in field : ---- what I want:
Jon L. Jones --- Jon
Lawrence Smith --- Lawre

Thanks,
Nancy
Click to expand...
Click to expand...
 
D

dtoney

Hi John,

On the same type of note, I would like to ask how to get the middle name? I
have my code working for first and last. The current name field is Doe, John
L and I have some that are Smith, Sally Jane so what I'd like to do is to
take the middle name or initial and put it in a separate field.

Dt
 
Top