We help IT Professionals succeed at work.

How to sort characters in a specific order

IsaacEin
IsaacEin asked
on
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?
Comment
Watch Question

IT Manager
Commented:
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.
CERTIFIED EXPERT
Top Expert 2011

Commented:
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 McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
CERTIFIED EXPERT

Commented:
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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.