[spudnik]

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]

Use the following Query

SELECT Left([Table1].[test],Instr([Table1].[test]," T/A")-1), Right([Table1].[test],Instr([Table1].[test]," T/A")-2)
FROM Table1;

[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!

[shabbir]

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]

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

Thanks again!

[shabbir]

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]

How do I put that string in?

The Right function is not working for some reason.

Many Thanks,
Graham

[shabbir]

Just try some simple sample with the right function and then go about with the actual problem.

[splatbasset]

Change right to mid That is what I did and the whole thing worked

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