How do I sort numbers correctly

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

Guest

How can I sort a field of numbers so that they show up in correct numerical
order.
I have numbers such as 1, 3, 15, 30a, 100, 356 etc.
When I sort the ascending order they are showing up as 1, 11, 2, 22, 200 etc.
I know that I can put zero's in front to make them all 3 digits long but I
don't want to do this.
Is there any way around it?
 
Paul said:
How can I sort a field of numbers so that they show up in correct
numerical order.
I have numbers such as 1, 3, 15, 30a, 100, 356 etc.
When I sort the ascending order they are showing up as 1, 11, 2, 22,
200 etc. I know that I can put zero's in front to make them all 3
digits long but I don't want to do this.
Is there any way around it?
Click to expand...

What you have are not numbers. They are strings that just happen to consist
(mostly) of digits so that is how they are sorting.

Try sorting on Val(FieldName)
 
Rick Brandt said:
What you have are not numbers. They are strings that just happen to consist
(mostly) of digits so that is how they are sorting.

Try sorting on Val(FieldName)
Click to expand...

Isn't Val(30a) going to be a problem?
 
On Wed, 19 Jan 2005 16:55:02 -0800, Paul Owen <Paul
How can I sort a field of numbers so that they show up in correct numerical
order.
I have numbers such as 1, 3, 15, 30a, 100, 356 etc.
When I sort the ascending order they are showing up as 1, 11, 2, 22, 200 etc.
I know that I can put zero's in front to make them all 3 digits long but I
don't want to do this.
Is there any way around it?
Click to expand...

What is 30a plus 25b equal to?

These ARE NOT NUMBERS. They are text, of course.

To sort the leading digits in numerical order, create a Query with a
calculated field:

SortKey: Val([fieldname])

This will give you a number (e.g. 30a and 30X will both give 30). Sort
by it, and (if you want 30a to sort before 30g) sort by the actual
field next in the sort order.


John W. Vinson[MVP]
 
Randy Harris said:
Isn't Val(30a) going to be a problem?
Click to expand...

Nope. From the Help file:
"The Val function stops reading the string at the first character it can't
recognize as part of a number."

In other words, Val("30a") will return 30. Unfortunately, so will Val("30b")
and Val("30z"), so you'll need to sort on both Val(FieldName) AND FieldName.

Note, though, that this won't work if the string starts with a non-number:
Val("a30") won't return a number.
 
Note, though, that this won't work if the string starts with a non-number:
Val("a30") won't return a number.
Click to expand...

Yes, it will... it'll return zero as the value.
 
Nope. From the Help file:
"The Val function stops reading the string at the first character it can't
recognize as part of a number."

In other words, Val("30a") will return 30. Unfortunately, so will Val("30b")
and Val("30z"), so you'll need to sort on both Val(FieldName) AND FieldName.
Click to expand...

The one little "gotcha" in this is that numbers in scientific notation
may cause startling results: e.g.

Val("30a10") = 30
Val("30b10") = 30
Val("30c10") = 30
Val("30d10") = 300000000000
Val("30e10") = 300000000000
Val("30f10") = 30


John W. Vinson[MVP]
 
Try sorting on Val(FieldName)<

Sorry to ask but I'm a novice at this - How do I sort on the above?
I can only find the option to sort ascending or descending.
I am working with the data in a form view but I only get the same options if
I try changing the sort in the table view as well.
 
Paul said:
Sorry to ask but I'm a novice at this - How do I sort on the above?
I can only find the option to sort ascending or descending.
I am working with the data in a form view but I only get the same
options if I try changing the sort in the table view as well.
Click to expand...

You add an additional column to the query based on the expression Val(FieldName)
and then sort on that.
 
Sorry - You've completely lost me. (I did say I was only a novice.)
I'm not using any queries - Just trying to sort a column in a table.

(I noticed your comment at the bottom of the message am I better replying
to that email address instead of to here?)
 
On Thu, 20 Jan 2005 16:51:02 -0800, Paul Owen <Paul
Sorry - You've completely lost me. (I did say I was only a novice.)
I'm not using any queries - Just trying to sort a column in a table.
Click to expand...

You're misunderstanding how tables work.

A table HAS NO ORDER.

It's an unordered "heap" of data.

If you want to see records in a particular sequential order, the
proper way to do so is to use a Query based on the table, sorted by
the field or expression which provides the desired order.

If you're using table datasheets to enter and view data... stop.
That's not what they're designed for. Instead, use a Query to select
and sort the data, and a Form to display it and edit it (or a Report
to print it).

John W. Vinson[MVP]
 
Bet your getting fed up of me but I;m still having difficulty.

I have now worked out how to do a query but I still cannot find a way of
sorting the field I want to sort on. I only get the ascending and descending
options.
How do I get to sort on the Val (fieldname) as was suggested in an
earlierpost.
I have tried in both dataview & design view but do not get any other options.
 
Paul said:
Bet your getting fed up of me but I;m still having difficulty.

I have now worked out how to do a query but I still cannot find a way
of sorting the field I want to sort on. I only get the ascending and
descending options.
How do I get to sort on the Val (fieldname) as was suggested in an
earlierpost.
I have tried in both dataview & design view but do not get any other
options.
Click to expand...

You need to add Val(fieldname) as a NEW field in the query and then sort on that
new field.
 
I have now worked out how to do a query but I still cannot find a way of
sorting the field I want to sort on. I only get the ascending and descending
options.
Click to expand...

You can only sort a field Ascending or Descending. There is no
Sideways. said:
How do I get to sort on the Val (fieldname) as was suggested in an
earlierpost.
Click to expand...

Open the Query in design view.
Select a vacant Field cell, or use the Insert menu option to insert a
new column in the grid (sorting proceeds left to right so if this is a
high priority sort among several, it must be to the left of the other
sorted fields).

Type

SortBy: Val([fieldname])

in the vacant Field cell.

Select either Ascending or Descending as the sort order for this
calculated field.

John W. Vinson[MVP]
 
I know I said there was light at the end of the tunnel in my previous post
thanking you for the help but - the light is fading again.

I have done as you suggested and created a query with the extra column with
Val (fieldname). This works great in as far as it now sorts in the numerical
order i.e.

1,3,5,20,36,99,200 etc. but it will not sort on the character bit i.e.

12a, 12b, 12c, 12d are coming out in random series such as 12b, 12c, 12a, 12d.

Any ideas how to get this last bit in the correct sequence?

John Vinson said:
I have now worked out how to do a query but I still cannot find a way of
sorting the field I want to sort on. I only get the ascending and descending
options.
Click to expand...

You can only sort a field Ascending or Descending. There is no
Sideways. said:
How do I get to sort on the Val (fieldname) as was suggested in an
earlierpost.
Click to expand...

Open the Query in design view.
Select a vacant Field cell, or use the Insert menu option to insert a
new column in the grid (sorting proceeds left to right so if this is a
high priority sort among several, it must be to the left of the other
sorted fields).

Type

SortBy: Val([fieldname])

in the vacant Field cell.

Select either Ascending or Descending as the sort order for this
calculated field.

John W. Vinson[MVP]
Click to expand...
 
For some reason my last message came up blank so I'll try again.

I created the query as you suggested and everything works fine - except -
although the numbers are now in the correct order, the characters are still
coming up as random i.e. 12d, 12x, 12b, 12m etc.

any further ideas to correct this?



John Vinson said:
I have now worked out how to do a query but I still cannot find a way of
sorting the field I want to sort on. I only get the ascending and descending
options.
Click to expand...

You can only sort a field Ascending or Descending. There is no
Sideways. said:
How do I get to sort on the Val (fieldname) as was suggested in an
earlierpost.
Click to expand...

Open the Query in design view.
Select a vacant Field cell, or use the Insert menu option to insert a
new column in the grid (sorting proceeds left to right so if this is a
high priority sort among several, it must be to the left of the other
sorted fields).

Type

SortBy: Val([fieldname])

in the vacant Field cell.

Select either Ascending or Descending as the sort order for this
calculated field.

John W. Vinson[MVP]
Click to expand...
 
Add the field that contains the full value to the query. Set its sort order
to Ascending. Deselect the Show checkbox.

Be sure that this field is to the right of the other sorting field so that
it is the second level of sorting logic.

--

Ken Snell
<MS ACCESS MVP>

Paul said:
For some reason my last message came up blank so I'll try again.

I created the query as you suggested and everything works fine - except -
although the numbers are now in the correct order, the characters are
still
coming up as random i.e. 12d, 12x, 12b, 12m etc.

any further ideas to correct this?



John Vinson said:
I have now worked out how to do a query but I still cannot find a way of
sorting the field I want to sort on. I only get the ascending and
descending
options.
Click to expand...

You can only sort a field Ascending or Descending. There is no
Sideways. said:
How do I get to sort on the Val (fieldname) as was suggested in an
earlierpost.
Click to expand...

Open the Query in design view.
Select a vacant Field cell, or use the Insert menu option to insert a
new column in the grid (sorting proceeds left to right so if this is a
high priority sort among several, it must be to the left of the other
sorted fields).

Type

SortBy: Val([fieldname])

in the vacant Field cell.

Select either Ascending or Descending as the sort order for this
calculated field.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
1,3,5,20,36,99,200 etc. but it will not sort on the character bit i.e.

12a, 12b, 12c, 12d are coming out in random series such as 12b, 12c, 12a, 12d.

Any ideas how to get this last bit in the correct sequence?
Click to expand...

As Ken says, you need to sort by both fields - the Val() calculated
field first, and then to its right in the sort order, the actual
field.

John W. Vinson[MVP]
 
Back
Top