Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Alphanumeric Post Reply Post New Topic
Author Message
thesmug1
Newbie
Newbie
Avatar

Joined: 12 Jun 2009
Online Status: Offline
Posts: 13
Quote thesmug1 Replybullet Topic: Alphanumeric
    Posted: 03 Aug 2009 at 7:58am
Hello everyone.  I am trying to sort some alphanumeric values.  For the strings that are alphanumeric, the letters will ALWAYS be in the first or first and second positions in each string.  For instance, D201987 and MP3928473.  The rest of the strings, which have varying lengths, contain only numbers.  Because the strings are not stored as numbers, however, I had to convert them.  So here is my code thus far.

If NumericText({TLORDER.BILL_NUMBER}[1 to 2])
    Then ToNumber({TLORDER.BILL_NUMBER})
Else {TLORDER.BILL_NUMBER}

However, I keep getting the error message that a number is required in place of {TLORDER.BILL_NUMBER} in the final line.

Ultimately my goal is a list that has the data sorted by numbers only strings (smallest to highest), then by the strings that begin with one letter, finally by the strings that begin with two letters.

Any help would be greatly appreciated.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Aug 2009 at 8:14am
You cannot do this conversion because the end reulst must allow all rows to be in the final data type (numeric). YOu could do something like
if isnumeric({TLORDER.BILL_NUMBER}) then tonumber({TLORDER.BILL_NUMBER}) else 0 but that will not help too much for your sorting...
 
My other suggestion did not work for you? It should.
If you add lead zeros to your text string and then make all the results the exact same length it will sort it numbers only first from lowest to hightest and then alpha.
Create a formula field called "SortOrder" as:
 
right("000000000000000" +{TLORDER.BILL_NUMBER},15)
IP IP Logged
thesmug1
Newbie
Newbie
Avatar

Joined: 12 Jun 2009
Online Status: Offline
Posts: 13
Quote thesmug1 Replybullet Posted: 03 Aug 2009 at 8:53am
Your code worked great!  Thanks a million.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.