Results 1 to 6 of 6
  1. #1
    Join Date
    May 2005
    Posts
    150

    Unanswered: All Text to the Right of '-'..any ideas?

    Ok when Terminals were entered into the system they were entered by VendorName-City,State

    So here is what I am trying to pull off

    I only want to show everything after the hyphen for a Vendor profile

    Example

    NEMF-Long Island, NY
    NEMF-Harrisburg, PA

    Shows :
    Long Island, NY
    Harrisburg, PA

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Use the
    Whereis = Instr(String,findit)

    Whereis = Instr(text,"-")

    then

    newbit = Mid(text,whereis+1, len(text))

    in oneline

    Mid(text,Instr(text,"-")+1, len(text))
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    May 2005
    Posts
    150
    thank you... worked perfectly

  4. #4
    Join Date
    May 2005
    Posts
    119
    You could also try:

    Right("NEMF-Long Island, NY",Len("NEMF-Long Island, NY")-InStrRev("NEMF-Long Island, NY","-"))

    This works well, as long as there is only one "-". You could do some if statements that would work around that problem.

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    InStrRev will not work for access 97
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Alternatively there is a naive approach
    if you know your dtaa is always prefixed with a 4 character code followed by a hyphen you could strip off the characters by using mid$(mycolumn,6).

    Personally its not an approach I'd reccomend (or use) as it will collapse if the vendor is not 4 characters, and or the hypenation has spacing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •