seprating name field

[ciscokid0690]

I have a table that has first and last name in one field. In order to cretae and sort address labels I need to seperate this field into Fname and Lname. I want to do this in an update query using simple functions. Anybody have any suggestions?

I have been searching the querry forum but have not found anything that directly answers my question.

Thanks

 

[Uncle Gizmo]

Look up "parsing" ...............................

 

[jeremie_ingram]

You can use the build function in the query builder to get what you are looking for.

For first names it goes like...
FName: Left([tblStudent]![stuName],(InStr([tblStudent]![stuName]," ")-1))

Here you find the space in the name field (InStr([tblStudent]![stuName]," ") and then use it as the number in the Left function. You end up retrieving everything to the left of the space.

For last names it is tricky, it is like...

LName: Right([tblStudent]![stuName],(Len([tblStudent]![stuName]))-(InStr([tblStudent]![stuName]," ")))

Here it is similliar, but you have to establish the length of the field forst (#of char) with this function
(Len([tblStudent]![stuName])

Then you SUBTRACT the number representing the locaiton of the space
-(InStr([tblStudent]![stuName]," ")

These two combined reveal the # of char it is that the last name occupies. Now you can use the RIGHT function to extract the exact # of char of the last name for the field. The RIGHT function starts at the right and goes towards the left the # of char you tell it to.

The above example is based off of a table called tblStudent which has both the first and last name of a person in a single field called stuName.

I hope this helps

 

[raskew]

Hi -

There may be an easier way. This debug (immediate) window example shows the processes for extracting first and last names:

Name = "Elvis Presley"
FName = left(name, instr(Name, " ")-1)
LName = mid(name, instr(Name, " ")+1)
? FName
Elvis
? LName
Presley

HTH - Bob

 

[ciscokid0690]

Thanks!!!!

Thanks for all the help. Your suggestions would justb great.