splitting text fild

[Michael Lindekugel]

Hello,

I have a table with a field called Name. the values in the Name field
consist of first names and last names separated by a space. the values vary
in length. I am having trouble finding a function or combination of
functions that allows me to split the field into two new fields of FirstName
and LastName. any suggestions?

cheers,
Michael

 

[Cheryl Fischer]

You can use the following expressions to get separate first names and last
names. Each involves looking for the position of a *single* space in the
FullName field:

FirstName: Left([FullName], InStr([FullName], " ") -1)

LastName: Mid([FullName], InStr([FullName], " ") +1)

This will work properly if all of your FullNames consist of a single first
name and a single last name separated by a space. It will return incorrect
results for names like:

Mary Anne Smith
John P. Van den Berg

hth,

 

[fredg]

Hello,

I have a table with a field called Name. the values in the Name field
consist of first names and last names separated by a space. the values vary
in length. I am having trouble finding a function or combination of
functions that allows me to split the field into two new fields of FirstName
and LastName. any suggestions?

cheers,
Michael

Michael,
If in fact the name of this field is [Name], I would strongly suggest
you change it to something else. Name is a reserved Access/VBA key
word. For more information read Microsoft KnowledgeBase article:
109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

To answer your question, it is important that ALL the records be in
the same format. If it is in
FirstName space LastName
order, then:

FirstName:Left([FullName],InStr(FullName]," ")-1)
LastName:Mid([FullName],InStr([FullName]," ")+1)

But change that [Name] field name now!!!

 

[Michael Lindekugel]

thank you to all who replied to my post.