Splitting Names and updating (1 Viewer)

[Cavern]

Just another quick (I hope) question...

I'm importing some information into a contact list from an old table, and I just realized that the names in the old table are stored as one string, while in the new one I need them in two seperate strings. I'm using an append query to bring over some other information, and I was wondering if there's some way to split the names at a ' ' (space) character and then place the two pieces (first and last name hopefully), into two seperate fields..... I think this is possible, I just dont know the functions to use...

thanks again
-Cavern

 

[The_Doc_Man]

If you are lucky and don't have middle names (thus TWO spaces in the same string...)

You can use Instr to find the first space, then Left$ and Right$ to find the text to the left and right of that space. You would have to use Trim$ to make sure that the first space wasn't also the first column.

You would also have to worry about whether there WAS no space because the answer given to you was just one name, not a first and last name.

 

[Cavern]

Okay... Just where should I be doing the processing?
Like can I just stick it in an SQL statement somewhere?
Or do I need to create a seperate function and work on the data one entry at a time?

Your reply already helps me a bunch tho, I'll keep plugging away at it.....
-Cavern

 

[Cavern]

Yeay! Success!!!
Thanks a bunch for your help, I finally got it working right..
I had to stick the code as an expression in my source field in my append query (where the 'Append To' was set to 'First Name')...
Here it is, in case someone has the same problem:

Trim(Left([Industrial Clients].[Name of 1st Contact],InStr(Trim([Industrial Clients].[Name of 1st Contact]),' ')))

Its ugly, but it gets the job done. All you have to do is swtich the 'Left' to 'Right' to get the last name...
-Cavern

 

[Cavern]

Okay.. It doesn't work as well as I'd hoped
It seems to be 'stretching' the last name to be the same length as the first (ie. 'AAA B' will be 'AAA' and 'AA B'), although if the last name is longer, then both names are fine.. I can't for the life of me figure out whats wrong...
Any suggestions?

 

[Cavern]

Hmmm.. It seems to be concatenating the second name if its shorter as well.. Ie. 'A BBBBB' becomes 'A' and 'BB'...

 

[Cavern]

I think I got it.. Apparently Left() takes an index from the left, and Right() takes an index from the right, but Instr() returns an index from the left.. Which is trouble when you're trying to use an index from the right..
So, had to switch the expression for finding the right-hand section to this:

Expr3: Trim(Right([Industrial Clients].[Name of 1st Contact],Len([Industrial Clients].[Name Of 1st Contact])-InStr(Trim([Industrial Clients].[Name of 1st Contact]),' ')))

All that I had to change was the InStr() part got changed to Len(of string) - Instr()... So that it was a number from the right hand side not the left.. Seems to be working now... 'Seems' being the keyword

 

[The_Doc_Man]

Sorry I didn't get back to you sooner, but at least you worked it out correctly.

Yes, Left starts counting from the left. Right starts from the right.

Which is why I always TRIM a field before doing any part of this. That way, Right$( string, N ) returns the rightmost N non-blank characters. It just makes the resulting nested list of nasties that much nastier.