Split text field

spudnik's Avatar
Newbie Member
Hi,

Would apprectaie some help with the following.

If I have a text field in access like

"Graham Murphy T/A The Big Tree"

How do I split the field into "Graham Murphy" and "The Big Tree"


Many thanks lads!
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Use the following Query
Code:
SELECT Left([Table1].[test],Instr([Table1].[test]," T/A")-1), Right([Table1].[test],Instr([Table1].[test]," T/A")-2)
FROM Table1;
0
spudnik's Avatar
Newbie Member
That works great for what is left of the 'T/A' but its not grabbing all of the right side of the string for some reason.

Thanks for the help though!
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Quote:
Originally Posted by spudnik
That works great for what is left of the 'T/A' but its not grabbing all of the right side of the string for some reason.

Thanks for the help though!
Thats because I have used -2 there use appropriate number depending on the number of pads in space.
0
spudnik's Avatar
Newbie Member
The field names are all different lengths. How do I grab everything to the right of T/A.

Thanks again!
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Instr([Table1].[test]," T/A") gets the index of [Table1].[test] string where T/A starts

Right([Table1].[test],Instr([Table1].[test],"T/A")-2) should get the content of the field after T/A

If it does not for any case put that string.
0
spudnik's Avatar
Newbie Member
How do I put that string in?

The Right function is not working for some reason.

Many Thanks,
Graham
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Just try some simple sample with the right function and then go about with the actual problem.
0
splatbasset's Avatar, Join Date: Oct 2011
Newbie Member
Change right to mid That is what I did and the whole thing worked

Mid([Table1].[Test],InStr([Table1].[Test]," ")) AS Last