nth smallest is sorting wrong

[LaurieHamlin]

I am using Crystal Reports 11 with an Access database. I need to pull a district number out of a string field then combine the various districts into a single string in order.
Taking the district out of the field works fine. When I take the nth smallest districts, I get:
11, 14, 16, 24, 1, 3, 4, 6, 7

instead of:
1, 3, 4, 6, 7, 11, 14, 16, 24

When I pull out the district, I tried both taking just the number and also taking the number and if it's <10 adding a 0 in front of it. It didn't make a difference.
I also tried using ToNumber(). I tried it in the formula that pulls the district out of the string. That didn't work. I tried it in the nth smallest formula. Still didn't work. I tried creating another formula that simply converts the district to number, and using that formula in the nth smallest formula and that didn't work.
I hope that's understandable.
Any ideas?
Thanks a million in advance.

 

[lbass]

Please show the content of the formulas you are currently using.

-LB

 

[LaurieHamlin]

{@dist}
If Length ({ToBeElected.District}) = 33 Then
Mid ({ToBeElected.District}, 20, 3)
Else
Mid ({ToBeElected.District}, 21, 1)

{@offterm})
{ToBeElected.Office} & {expires}

{@dists}
Select Count ({ToBeElected.Office}, {@offterm})
Case 1: Minimum ({@dist}, {@offterm})
Case 2: Minimum ({@dist}, {@offterm}) & ", " & Maximum ({@dist}, {@offterm})
Case 3: Minimum ({@dist}, {@offterm}) & ", " & NthSmallest (2, {@dist}, {@offterm}) & ", " & Maximum ({@dist}, {@offterm})

that goes up to 16, but obviously it gets very long

Thanks.

 

[lbass]

Try changing the formula to:

If Length ({ToBeElected.District}) = 33 Then
totext(val(Mid ({ToBeElected.District}, 20, 3)),"00") Else
totext(val(Mid ({ToBeElected.District}, 21, 1)),"00")

If that still doesn't work, please show sample data that includes the other fields, all labeled.

-LB

 

[LaurieHamlin]

Thanks! That got them into order. I just had to add
Right (Minimum ({@dist}, {@offterm}), 1) & ", " & Right (NthSmallest (2, {@dist}, {@offterm}), 1) & ....
to get it to display as 1, 3, 4, 6, 7, 11, 14, 16, 24
instead of 01, 03, 04, 06, 07, 11, 14, 16, 24
easy enough
I appreciate the help.

 

[lbass]

You could have simply used the formula I suggested for sorting, but used the actual field for display.

-LB

 

[LaurieHamlin]

LB,
I ended up doing that because the
Right (NthSmallest (2, {@dist}, {@offterm}), 1)
made such a long formula.
Thanks.