Parsing string from Right to Left

How can I create a substring of the characters to the right of the last
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/9912-parsing-string-from-right-to-left.html
blank space in the string. I would like to be able to use an expression to do
this in query designer.
Thanks

*

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

Try using InStrRev to do it

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

--
Good Luck
BS"D


"JoeA2006" wrote:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9912

> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9912
> this in query designer.
> Thanks

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:

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9912
> Try using InStrRev to do it
>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9912
> 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

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" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9912
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9912
expression to do
> > > this in query designer.
> > > Thanks