Sorting Report Field- Alphanumeric

[Terry Lo via AccessMonster.com]

We have a report with lab results that have to be sorted in ascending order.
The problem is that the results can look like 0.001 or 0.001U for example.
The result with the U has to come first, but obviously it doesn't cause
Access treats it as a larger number. Any "easy" solutions. This is Access
97. Thanks.

 

[Duane Hookom]

Considering all the sample data you provide, just sort descending. If you
have more sample records, please provide a wider variety so that we have a
more comple understanding. We don't know if there are always 3 decimal
places or if there are numbers greater than 1 or whatever.

 

[Terry Lo via AccessMonster.com]

There results in that field could be for example:
0.001
0.001U
0.002U
0.003
0.005
0.007U
0.004

In terms of the results, numbers with a U are lower results so we would want
that arrangement to look like
0.001U
0.001
0.002U
0.003
0.004
0.005
0.007U

 

[Duane Hookom]

Set your sorting and grouping levels:

=Left([YourField],5) Ascending
=Mid([YourField],6) Descending