Sorting on multiple number & letter/number combinations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field that I want to sort by in my report. The possible values
comprise the numbers 1 through 9, 10S, 10N, 11, 12, "claims," "NYC," and
"OCA."

The report insists on listing the categories "10N" and "10S" in between the
numbers 1 and 2. How do I change this so that "10N" and "10S" fall in
between categories "9" and "11" on the report?

I think this has something to do with changing the data type on the original
table in which these values are established, but beyond that I'm a bit lost.

Many thanks.
 
Jim J. said:
I have a field that I want to sort by in my report. The possible values
comprise the numbers 1 through 9, 10S, 10N, 11, 12, "claims," "NYC," and
"OCA."

The report insists on listing the categories "10N" and "10S" in between
the
numbers 1 and 2. How do I change this so that "10N" and "10S" fall in
between categories "9" and "11" on the report?

I think this has something to do with changing the data type on the
original
table in which these values are established, but beyond that I'm a bit
lost.
Click to expand...

You are getting normal character sorting but seem to want something else
("neither fish nor fowl")... and since you have alphabetic characters, that
is the type of field. When I wanted a non-alphabetic, non-numeric sort, I
defined a separate table for sort order, with the code, and the sort order
for that code, linked it to the table with the data by the code, and sorted
on the sort order field.

Larry Linson
Microsoft Access MVP
 
try adding a sort level based on the Value Function.

Sort Level 1: Value(YourField)

Sort Level 2: YourField
 
So far, so good. I've added a new table that provides numerical values to
each of the corresponding fields that I want to sort on.

The query that results is exactly what I want - it sorts in ascending
values, just like I want it to.

However, I can't get the report to follow suit - it still sorts in the wrong
order, just like it does before. Is it because I'm doing something
incorrectly with the sorting and grouping function?

I feel like the answer is right there in front of me; I'm just not sure what
it is.

Many thanks for your help.
 
Back
Top