Re: string manipulating function?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

Eric wrote:
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

"Paolo" wrote:

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

"Eric" wrote:

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.
.