Solved

# Sort Alphanumeric Numbers

Posted on 2005-04-18
1,034 Views
I have an SQL database that Crystal Reports 10 creates reports from.  It does not sort the Unit Numbers the way I want them to sort.  They are alphanumeric.  Crystal sorts like this:

10-D
100-B
20-F
231-C
40

I want to sort like this:

10-D
20-F
40
100-B
231-C

I found out how to do this in SQL and it is done like this:

ORDER BY CAST(LEFT(tblRenDet.RenDUnitNo, ISNULL(NULLIF(CHARINDEX('-', myfield) - 1, -1), "LEN(myfield))) AS INT), myfield

Does anybody know how to do this in Crystal Reports? Thanks.
0
Question by:molard

LVL 28

Expert Comment

Create a formula that returns the numeric value of the field:

//@Alpha Sort
val({table.field})

Sort on this formula rather than the actual field.
0

LVL 77

Expert Comment

Hi molard,
Create a formula field which gets the numeric value and sort on the formula field:

@Mysort:

val({mytable.myfield})

Pete
0

Author Comment

It sorts the ones that are only numeric correctly but the alpha numeric ones it still sorts as if it were text instead of the way I would like. If I sort ascending, it sorts all the alpha ones as text and it has all of those together.  It then sorts all the numeric ones correctly.  For example half of my report has the alpha ones sorted at the top and the rest of the report has the numeric ones sorted.

I created a formula called AlphaSort that consists of:

val({mytable.myfield})

I chose to sort by the formula AlphaSort

Great suggestions from the both of you.  Can you think of something else I could try?
0

LVL 9

Expert Comment

create a formula that does about the same:

tonumber(left({FIELD},InStr ({FIELD},"-")-1)

then order by this formula and then {FIELD}
0

LVL 9

Expert Comment

and add a closing ) at the end of the formula :-)

hth,

Outin
0

LVL 28

Expert Comment

How large can the number portion get?
This formula will work by adding leading zeroes to the number portion:

//@AlphaSort

totext(split({table.field},"-")[1],"00000") & "-" & split({table.field},"-")[2]
0

Author Comment

Outin,

I tried that but I get an error that says:

String length is less than 0 or not an integer.

Thanks for the try.

bdreed35,

The number portion does not get higher than 900.  I will try your formula as well.

Thanks.
0

LVL 9

Expert Comment

Before using my formula or the formula bdreed35 proposed, you should check on the presence of a "-" in the field, otherwise an error is generated:

If {FIELD} like "*-*" then
tonumber(left({FIELD},InStr ({FIELD},"-")-1))
else {FIELD}

--
Outin
0

LVL 9

Expert Comment

Hmm thanks to slow network here you were quicker :-)

--
Outin
0

LVL 9

Accepted Solution

Tss it must be monday..

should be

If {FIELD} like "*-*" then
tonumber(left({FIELD},InStr ({FIELD},"-")-1))
else tonumber({FIELD})

I assume there is only data like
10-A
10
but no data like
A
10A
etc.
0

Author Comment

You did it Outin!  Thanks!
0

LVL 9

Expert Comment

Btw it's always nice to see how one problem can have different approaches to find a solution...

--
Outin
0

## Featured Post

### Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…