Seperate first and last name

[John]

Good Day all
I have a single field that has both the first and last names in it.
In some records we even have a middle initial.
I was thinking of using the Left (Fullname,XX) function to get the first
name but as all the names have different lenghths up to the first space
between names, I need to replace the XX which tells the function to go as
far as the first space only.
Please point me in the right direction.
Thanks

[onnodb]

Just from the top of my head: I think you should have a look at the
Pos function --- you can use it to determine the position of the first
space. Also, one tip that might be handy: you can try these things out
in the "Direct" window in the VBA editor (press Ctrl+G to show it).

Good luck!

Yours sincerely,
Onno Broekmans

[Daveo]

Hi John,

Try:

Forename = Left([fullName],InStr([fullName]," "))

Surname = Right([fullName],Len([fullName])-InStr([fullName]," "))

Cheers - David

[Bill]

On 12 Feb 2007 05:17:17 -0800, "Daveo" <wr**********@gmail.comwrote:

>Hi John,

Try:

Forename = Left([fullName],InStr([fullName]," "))

Surname = Right([fullName],Len([fullName])-InStr([fullName]," "))

Cheers - David

I usually use the Split command in VB for this.

If you split on a space, the function will return an array of
substrings. Depending on the name, you will get different string
counts.

Examples:
Fred Smith

Fred Von Smith

Fred Smith, Jr.

Fred Von Smith, Jr.

You may have to do a little work with your array but once you get the
names fixed you will be a lot better off.

Nothing like doing a search for "Bill Smith" and find that his reall
name is John but people have called him Bill for so long that it just
stuck except that your database has John. - This story is adapted
from a real life situation.

Bill

[John]

Thanks Guys
As usual the response from this group is awesome.