How to sort characters in a specific order

Posted on 2012-08-16
Last Modified: 2012-08-17
I have a pick sheet report that I want to sort a location field by a string field, but within that string field there is a date and and a quantity all entered in a specific order. So what I need to do is sort the date by year first, then month, and finally day. The date is entered in this format 08/16/12 (month/day/year) and right now it is sorting by month, day, year. So what I am wondering, is if there is way to tell Crystal in a formula to sort by specific character placements? Can I tell it to sort by the 7th character first, then the 8th, then the first, then the second and finally the fourth and fifth?
Question by:IsaacEin
    LVL 5

    Accepted Solution

    Give this a try as a formula:
    stringvar sort1 := mid({Table.String},7,1);
    stringvar sort2 := mid({Table.String},8,1);
    stringvar sort3 := mid({Table.String},1,1);
    stringvar sort4 := mid({Table.String},2,1);
    stringvar sort5 := mid({Table.String},4,1);
    stringvar sort6 := mid({Table.String},5,1);
    stringvar sortall := sort1 + sort2 + sort3 + sort4 + sort5 + sort6;

    Open in new window

    Then sort by that formula.
    LVL 18

    Expert Comment

    You can create a SQLExpression like this :

    The syntax is for SQLServer and will convert values like '08/16/12' to '2012/08/16'
    Sort by the SQLExpression.
    LVL 100

    Expert Comment

    I think you need to get the left 8 characters before converting to a date


    Sort on that formula

    LVL 34

    Expert Comment

    FWIW, if you want to use a formula like the one that JasonSchlueter suggested, you could shorten it to:

    stringvar sort1 := mid({Table.String},7,2);
    stringvar sort2 := mid({Table.String},1,2);
    stringvar sort3 := mid({Table.String},4,2);

    stringvar sortall := sort1 + sort2 + sort3


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
    I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    755 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

    18 Experts available now in Live!

    Get 1:1 Help Now