Sorting Report Field- Alphanumeric

  • Thread starter Thread starter Terry Lo via AccessMonster.com
  • Start date Start date
T

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.
 
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.
 
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
 
Set your sorting and grouping levels:

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