Hi all,



I have this database, but the person who created it didn't make a separate 

field for first and last name (doh) -- I want to be able to split out the 

single field into two (or three) different fields. 



anyone got a good starting place on how I might do that?



thanks in advance,



J.

J,

Assuming there is a space between the names:

You can either write an Update query in code or in the query builder.

1. Duplicate the data in a second field so that when you update the

first field you still have the last names.

2. In the query builder the expression will look some like this (This

will go in the Update To line of the query:

Left(YourCurrentFieldName,Instr([YourCurrentFieldName]," ")-1)

The Instr function will return the position of the space and you are

subtracting one from that, and getting everything to the left of it

which should be the first name.

To update the last name field just add one instead of subtracting one.

Hope this helps,

Richard Lobel

You can use the InStr functiion to specify the position of the first 

occurrence of a space within the full name and use the position in your 

Left and Right functions to split first and last names. Mind you this will 

not work correctly for all names, ie. John Van Smith, but it will work for 

most.



For example:



Function NameSplitter (strFullName)

    Dim intPos as integer

    Dim intFullNameLen as integer

    Dim strFName as string

    Dim strSurname as string



    const strSPACE = CHR(32)



    intPos = Instr(1, strFullName, strSPACE )  ' Search for for a first 

occurance of space



    intFullNameLen = Len(strFullName)	  'Get FullName lenght



    strFName = Left(strFullName, intPos - 1) ' Retrieve first name



    strSurname =D Right (strFullName, intFullNameLen - intPos) 'Retrive 

surname



     'Any other code you may need to use...



End Function



    That should work for most names except the ones with more then one 

space in the full name. They can be manually edited...





_____________________________________



John Fejsa

Systems Analyst/Computer Programmer

Hunter Centre for Health Advancement

Locked Bag 10

WALLSEND NSW 2287

Phone: (02) 49246 336 Fax: (02) 49246 209

________________________________________





>>> jsaam@m... 21/07/2001 5:34:13 >>>

Hi all,



I have this database, but the person who created it didn't make a 

separate field for first and last name (doh) -- I want to be able to split

 out the single field into two (or three) different fields.



anyone got a good starting place on how I might do that?



thanks in advance,



J.

---

I'd start out w/the Split() function in VB--see the help file for details.

I imagine taking the first array element returned as the first name & the

last as the last name would be a decent starting point (tho you may have to

screen out Jr.'s, M.D.'s and the like).



HTH,



-Roy



-----Original Message-----

From: Jon [mailto:jsaam@m...]

Sent: Friday, July 20, 2001 12:33 PM

To: Access

Subject: [access] Split Data in one field into two different fields





Hi all,



I have this database, but the person who created it didn't make a separate 

field for first and last name (doh) -- I want to be able to split out the 

single field into two (or three) different fields. 



anyone got a good starting place on how I might do that?



thanks in advance,



J.