Parse everything to Right of slash or to the left

Posted on 2012-08-15
Last Modified: 2012-08-15
I have a query field [BEN-COV-OPTION] with values like

I need to split this out into two fields.  I need an expression that will return everything to the left of the slash mark regardless of how many characters it may be and I need an expression that will return everything to the right of the slask mark regardless of how many characters it may be.  Any help would be most appreciated.
Question by:jph826
    LVL 65

    Expert Comment

    by:Jim Horn
    'Slash character position
    ?InStr(1, "$5000/$2500", "/")

    'Everything left of Slash
    ?Left("$5000/$2500", InStr(1, "$5000/$2500", "/") - 1)

    'Everything right of Slash
    ?Right("$5000/$2500", Len("$5000/$2500") - InStr(1, "$5000/$2500", "/"))
    LVL 65

    Expert Comment

    by:Jim Horn
    LVL 92

    Accepted Solution


        Left([BEN-COV-OPTION], InStr(1, [BEN-COV-OPTION] & "/", "/") - 1) AS xLeft,
        Mid([BEN-COV-OPTION], InStr(1, [BEN-COV-OPTION] & "/", "/") + 1) AS xRight
    FROM [SomeTable]

    Open in new window

    In that, if there is no "/" character, then the whole thing goes to xLeft, and xRight comes back as a zero length string.

    jimhorn's suggestions above will return an error for the "left" is there is no slash, and will put everything into the "right" if there is no slash.  That might or might not be closer to what you want :)

    Author Comment

    Thank you both for responding so quickly!  

    Thank you matthewspatrick, that worked perfectly.  I forgot to state that the [BEN-COV-OPTION] field could also be blank, and for those I was getting "#Error".  You had the foresight to consider that as well.  Thanks again!

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now