• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 568
  • Last Modified:

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?
0
IsaacEin
Asked:
IsaacEin
1 Solution
 
Jason SchlueterIT ManagerCommented:
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.
0
 
vastoCommented:
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.
0
 
mlmccCommented:
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
0
 
James0628Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now