Access 2007 - Instr
Posted on 2011-09-25
My OS is windows 7 and I use Access 2007. I have a Field which is called Name is a Text type.
The data in this field contains the first, middle and last name separated by a space.
I would like to separate the first and middle names and put them in a field called First Name
And the last name and put them in a field caleld LastName.
I am trying to use an ADO function to loop thru all the names, and then find the spaces.
Thus if the data has only one space, then the first part would have to be the first name and the rest goes into the last name.
But if the data has more than one spaces, i.e. eg : John Phil Smith, then John Phil would go into the first name and Smith to the last name.
Is using the InStrr method to find this space the correct way to do it.
The the code to my mind would be like :
If 1 space, Then
take all data to the left of that space and put it into a var called First Name
and all data to the right of that space, put it into a var called Last Name
Now update the record in the Table.
Go on looping until end of file.
Hope some expert could help me with the coding, and if possible also the ADO code.
The name of the Table is called Seniors Club
The field name where the data is to be extracted from is called Name, and is a text type field.
The firstname field is text type and also the lastname field is also text type.