Split firstname, lastname

[tweetyksc]

I have data from a customer that I've imported into access.
They have put the first name and last name in one field; I want to extract the first name/last name to two different fields so I can run reports, check for duplicates, etc.

What's the easiest way to do this?

 

[jekirksey]

everyone is probably going to give you a lot of code to do this, but if it is as simple as firstname<space>lastname then I would export the table to comma delimited (text file) then open it in MS Word and do a find and replace for the <space> with a comma (or other delimiter) and then import it into your table, simple, but effective.

 

[raskew]

After adding fname and lname fields to your table, you can use an update query, provided the names are consistent with just a first name and a last name separated by a space.

For the fname field, update to (I've used FullName as the name of your current field. Change it as required):

Left(fullname, instr(fullname, " ")-1)

For the lname field:

mid(fullname, instr(fullname, " ")+1)

 

[tweetyksc]

I took the data and did a find/replace with the text file and reimported as jekirksey said. This was something manual, of course, but it solved the problem.

raskew - I looked at yours to see if I can do this in the program if it comes up again; I don't see how the instr would work as wouldn't it just pull the number (location) of the space?
Of course, then this number could be used to define how much to pull out with a left function, etc.

I just would think there would be an easier way to do this.

 

[neileg]

I don't see how the instr would work as wouldn't it just pull the number (location) of the space? Of course, then this number could be used to define how much to pull out with a left function, etc.

That's exactly how it works! How much easier do you want it to be?