Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MS Access (http://www.go4expert.com/forums/ms-access-forum/)
-   -   Split text field (http://www.go4expert.com/forums/split-text-field-t1060/)

spudnik 24Jul2006 16:39

Split text field
 
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!

shabbir 24Jul2006 18:04

Re: Split text field
 
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;


spudnik 24Jul2006 19:39

Re: Split text field
 
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!

shabbir 24Jul2006 19:48

Re: Split text field
 
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.

spudnik 24Jul2006 20:19

Re: Split text field
 
The field names are all different lengths. How do I grab everything to the right of T/A.

Thanks again!

shabbir 24Jul2006 22:35

Re: Split text field
 
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.

spudnik 31Jul2006 15:39

Re: Split text field
 
How do I put that string in?

The Right function is not working for some reason.

Many Thanks,
Graham

shabbir 31Jul2006 16:33

Re: Split text field
 
Just try some simple sample with the right function and then go about with the actual problem.

splatbasset 20Oct2011 22:38

Re: Split text field
 
Change right to mid That is what I did and the whole thing worked

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


All times are GMT +5.5. The time now is 23:04.