string manipulating function?

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

I'm looking for a way to split a field into 2 values, my field always
contains the 2 varaibles FullName - Office, I want to split it to FullName
and to Office into 2 disctintive fields.

For example: John Doe - HisOffice
should become:

Jon Doe
Office

Any help is appreciated.
 
Hi Eric,

you can do in this way:
let's say the field containing the values's called yourfield:

var1 (with your example=John Doe)=trim(left(yourfield,instr(yourfield,"-")-1))
var2 (with your example=HisOffice)=trim(mid(yourfield,instr(yourfield,"-")+1))

HTH Paolo
 
Paolo,

It works fine for most of the names but a have full names
that contains the hyphen symbol - and that causes problems.

For example if I have the full name : JOHN-DOE KERKORIAN
Then it will only move JOHN as result.
Thanks
 
IF your field ALWAYS has a - between the Name and the office and it ALWAY has
both parts AND you are using Access 2000 (fully patched) or later you can use
the instrRev function in place of the InStr function.

trim(left(yourfield,instrRev(yourfield,"-")-1))
trim(mid(yourfield,instrRev(yourfield,"-")+1))

Of course, that fails if the office part also can have a dash.
John Doe - Hale-Thorpe Limited
would return
John Doe - Hale
Thorpe Limited

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Well, if you always have a space before and after the hyphen that separate
the two fields modify what I sent before in this way

var1 (with your example=John Doe)=trim(left(yourfield,instr(yourfield," -
")-1))
var2 (with your example=HisOffice)=trim(mid(yourfield,instr(yourfield," -
")+1))

Cheers
 
Use the suggested code to start. Then check the field that is supposed to
have the office # and see if it has a '-'. If so, this means that the name
was split up into two pieces , with the part after the first '-' stored in
the office # field along with the actual office #. So repeat the same process
on the field that is suposed to be the office # and set the part before the
2nd '-' to NamePart2, and the part after the second '-' to the Office #. Then
concatenate the original Name with Name2 to form the full name.
 
Back
Top