Decrement last characters of a string

Posted on 2012-08-29
Last Modified: 2012-08-29
I have a string like this: "2012-13". I want to reduce the last characters on each side of the hyphen by 1 so that it becomes 2011-12. The string represents the current payment year and is stored in a setup table. I want to be able to write a query that will allow me to enter in the criteria row payments recorded for the current payment year and the prior payment year. ie, 2012-13 and 2011-12. Can anyone provide a solution for this that I could use?
Question by:dinmatt
    LVL 48

    Accepted Solution

    Use this expression:

    LastPeriod: Val([CurrentPeriod])-1 & -(Val([CurrentPeriod])-2000)


    Author Closing Comment

    Thank you gustav, that works perfectly and is easy to implement!
    LVL 48

    Expert Comment

    by:Gustav Brock
    You are welcome!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

    758 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

    13 Experts available now in Live!

    Get 1:1 Help Now