asked on

How to sort characters in a specific order

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?

ASKER CERTIFIED SOLUTION

Jason Schlueter

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vasto

You can create a SQLExpression like this :
convert(VARCHAR,convert(DATETIME,{Table.String}),111)

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

Mike McCracken

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

Date(Left(Table.String,8))

Sort on that formula

mlmcc

James0628

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

James