Split text field

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

  1. spudnik

    spudnik New Member

    Joined:
    Jul 24, 2006
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    0
    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

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    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

    Joined:
    Jul 24, 2006
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    0
    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

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    Thats because I have used -2 there use appropriate number depending on the number of pads in space.
     
  5. spudnik

    spudnik New Member

    Joined:
    Jul 24, 2006
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    0
    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

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    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

    Joined:
    Jul 24, 2006
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    0
    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

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    Just try some simple sample with the right function and then go about with the actual problem.
     
  9. splatbasset

    splatbasset New Member

    Joined:
    Oct 20, 2011
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice