splitting text fild

  • Thread starter Thread starter Michael Lindekugel
  • Start date Start date
M

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
 
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,
 
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
Click to expand...

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!!!
 
Back
Top