1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Split text field

Discussion in 'MS Access' started by spudnik, Jul 24, 2006.

  1. spudnik

    spudnik New 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!
     
  2. shabbir

    shabbir Administrator Staff Member

    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;
     
  3. spudnik

    spudnik New 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!
     
  4. shabbir

    shabbir Administrator Staff Member

    Thats because I have used -2 there use appropriate number depending on the number of pads in space.
     
  5. spudnik

    spudnik New Member

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

    Thanks again!
     
  6. shabbir

    shabbir Administrator Staff Member

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

    spudnik New Member

    How do I put that string in?

    The Right function is not working for some reason.

    Many Thanks,
    Graham
     
  8. shabbir

    shabbir Administrator Staff Member

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

    splatbasset New Member

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

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

Share This Page