Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Parsing string from Right to Left

Discussion in 'Information Technology' started by JoeA2006, Jul 28, 2006.

  1. JoeA2006

    JoeA2006
    Expand Collapse
    Guest

    How can I create a substring of the characters to the right of the last
    blank space in the string. I would like to be able to use an expression to do
    this in query designer.
    Thanks
     
  2. Loading...

    Similar Threads Forum Date
    India Akal Takht Chief Wants Obama to Take Stringent Measures to Check Hate Crimes against Sikhs Breaking News Sep 25, 2013
    Preserving Ancient String Instruments Gurmat Sangeet Feb 28, 2011
    Vibrating String Interfaith Dialogues Dec 14, 2007
    Sikh News String of welfare measures for Dalits in Punjab (Outlook India) Breaking News Oct 26, 2007

  3. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    Try using InStrRev to do it

    Right([FieldName],InStrRev ([FieldName]," ")-1)

    --
    Good Luck
    BS"D


    "JoeA2006" wrote:

    > How can I create a substring of the characters to the right of the last
    > blank space in the string. I would like to be able to use an expression to do
    > this in query designer.
    > Thanks
     
  4. JoeA2006

    JoeA2006
    Expand Collapse
    Guest

    This expression truncated the first n of characters returned from the
    InStrRev from the begining of the field.

    My data looks like this:
    xxxxxxxx xxxxxxxx xxx-x

    this expression returns
    xxx xxxxxxxx xxx-x

    what I need to see is
    xxx-x

    Do I need to change something in the expression?





    "Ofer Cohen" wrote:

    > Try using InStrRev to do it
    >
    > Right([FieldName],InStrRev ([FieldName]," ")-1)
    >
    > --
    > Good Luck
    > BS"D
    >
    >
    > "JoeA2006" wrote:
    >
    > > How can I create a substring of the characters to the right of the last
    > > blank space in the string. I would like to be able to use an expression to do
    > > this in query designer.
    > > Thanks
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    While InStrRev looks from right-to-left, the value it returns is still the
    character count from the left.

    In other words, if you're trying to find the last blank in the string, you'd
    use

    InStrRev(MyString, " ")

    For the case you're citing, that would return 18.

    To only get what's after that space, you want:

    Mid(MyString(InStrRev(MyString, " ") + 1)


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "JoeA2006" <JoeA2006@discussions.microsoft.com> wrote in message
    news:9610C7C8-F8EE-4132-BD50-F8291411D555@microsoft.com...
    > This expression truncated the first n of characters returned from the
    > InStrRev from the begining of the field.
    >
    > My data looks like this:
    > xxxxxxxx xxxxxxxx xxx-x
    >
    > this expression returns
    > xxx xxxxxxxx xxx-x
    >
    > what I need to see is
    > xxx-x
    >
    > Do I need to change something in the expression?
    >
    >
    >
    >
    >
    > "Ofer Cohen" wrote:
    >
    > > Try using InStrRev to do it
    > >
    > > Right([FieldName],InStrRev ([FieldName]," ")-1)
    > >
    > > --
    > > Good Luck
    > > BS"D
    > >
    > >
    > > "JoeA2006" wrote:
    > >
    > > > How can I create a substring of the characters to the right of the

    last
    > > > blank space in the string. I would like to be able to use an

    expression to do
    > > > this in query designer.
    > > > Thanks
     

Share This Page