Sorting Help

  • Thread starter Thread starter msmiley
  • Start date Start date
M

msmiley

I am trying to sort a field in query, and it works with numbers 1-9, but
when I enter a 10, it won't sort right. The sort comes out like this:
1, 10, 2, 3, 4, 5, 6, 7, 8, 9. I need it to look like this: 1, 2, 3, 4,
5, 6, 7, 8, 9, 10.

Can anyone help?
 
The problem is that apparently your underlying field is set
to be a text datatype instead of numeric. This is how text
fields sort. You need to change this in the table design
view. You should probably make a copy of the table first to
make sure that it converts properly.

Gary Miller
Sisters, OR
 
It sounds like your Field is a Text Field and Text value will sort "1",
"10", "2", "3" ... (you don't see the double-quotes, of course).

Either change your Table Field to Integer or Long if they are numerical
values or in the Query, use a calculated Field:

NumValue: Val([YourField])

which converts to numerical value and order the Records according to
Val([YourField]).
 
Access is telling me that that's not a valid expression. The field that
I'm trying to sort starts with a number and has a title after it, so
the field is set to text. So it's like a table of contents. Any other
suggestions?
 
If that is the case, you won't be able to convert to numeric
and you won't be able to sort on it correctly as-is unless
you add another sort field for the purpose which would be a
real pain to maintain. You could consider a design change
and split that into two fields and then concantenate them
for display purposes as needed.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
Smiley,

If the first couple of characters of the field are always integer
values, you could use an order by clause that looks something like:

ORDER BY CINT(LEFT([yourField], 2)

If the numbering scheme is like 1.0, 1.1, 1.3, then you will need to
figure out how many characters you can count on being numeric, then
convert them to double with the cDbl() function.

--
HTH

Dale Fye


Access is telling me that that's not a valid expression. The field
that
I'm trying to sort starts with a number and has a title after it, so
the field is set to text. So it's like a table of contents. Any other
suggestions?
 
Back
Top