How do I sort numbers correctly

[Paul Owen]

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?

 

[Rick Brandt]

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?

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)

 

[Randy Harris]

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)

Isn't Val(30a) going to be a problem?

 

[John Vinson]

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?

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]

 

[PC Datasheet]

What number comes between 29 and 31? What two numbers does 30a come between?

 

[Douglas J. Steele]

Isn't Val(30a) going to be a problem?

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.

 

[Ken Snell [MVP]]

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

Yes, it will... it'll return zero as the value.

 

[John Vinson]

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.

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]

 

[Paul Owen]

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.

 

[Rick Brandt]

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.

You add an additional column to the query based on the expression Val(FieldName)
and then sort on that.

 

[Paul Owen]

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?)

 

[John Vinson]

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.

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]

 

[Paul]

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.

 

[Rick Brandt]

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.

You need to add Val(fieldname) as a NEW field in the query and then sort on that
new field.

 

[John Vinson]

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.

You can only sort a field Ascending or Descending. There is no

How do I get to sort on the Val (fieldname) as was suggested in an
earlierpost.

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]

 

[Paul]

Ahh - There is light at the end of the tunnel at last.

Thanks for all the help.

 

[Paul]

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?

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.

You can only sort a field Ascending or Descending. There is no

How do I get to sort on the Val (fieldname) as was suggested in an
earlierpost.

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]

 

[Paul]

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?

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.

You can only sort a field Ascending or Descending. There is no

How do I get to sort on the Val (fieldname) as was suggested in an
earlierpost.

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]

 

[Ken Snell [MVP]]

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>

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?

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.

You can only sort a field Ascending or Descending. There is no

How do I get to sort on the Val (fieldname) as was suggested in an
earlierpost.

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]

 

[John Vinson]

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?

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]