Sorting numbers that are text fields

[Guest]

Thanks so much for any help I can get. Here is an example:
This is how I want it sorted.
1999
0102
0203
0405
0506

If I sort in assending order it sorts like this:
0102
0203
0405
0506
1999

If I sort in decending order it sorts like this:
1999
0506
0405
0203
0102

 

[Duane Hookom]

What's the calculation/reason for the "how I want it sorted"? You could set
a sorting and group expression to something like:
=IIf( [YourTextField] = "1999", 0 ,[YourTextField])

While this would provide your specified result, I doubt it is what you want.

 

[Rick Brandt]

Thanks so much for any help I can get. Here is an example:
This is how I want it sorted.
1999
0102
0203
0405
0506

If I sort in assending order it sorts like this:
0102
0203
0405
0506
1999

If I sort in decending order it sorts like this:
1999
0506
0405
0203
0102

Is "1999" the only exception? If so, add a calculated field to the query...

SortVal: IIf([FieldName]="1999", 0, Val([FieldName]))

....then sort on that calculated field in ascending order.

 

[Rick B]

I don't understand your sort. 1999 is bigger than 102. Even if you look at
it from a text standpoint, the 1 is bigger than the zero. You want to sort
descending on the first digit (1, then 0), then ascending on the last three
(102, then 203, then 405)?

I don't get it.