Print Page | Close Window

Alphanumeric

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7230
Printed Date: 03 May 2024 at 10:58pm


Topic: Alphanumeric
Posted By: thesmug1
Subject: Alphanumeric
Date 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.


Replies:
Posted By: DBlank
Date 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)

Posted By: thesmug1
Date Posted: 03 Aug 2009 at 8:53am
Your code worked great!  Thanks a million.


Print Page | Close Window