How do I sort numbers correctly

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

"Rick Brandt" wrote:

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

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

On Thu, 20 Jan 2005 16:51:02 -0800, Paul Owen Paul
wrote:

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]

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.

"John Vinson" wrote:

On Thu, 20 Jan 2005 16:51:02 -0800, Paul Owen Paul
wrote:

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 wrote:
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.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

On Mon, 24 Jan 2005 16:37:05 -0800, "Paul"
wrote:

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
Sideways. g

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]

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

Thanks for all the help.


"Rick Brandt" wrote:

Paul wrote:
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.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

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" wrote:

On Mon, 24 Jan 2005 16:37:05 -0800, "Paul"
wrote:

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
Sideways. g

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]

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" wrote:

On Mon, 24 Jan 2005 16:37:05 -0800, "Paul"
wrote:

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
Sideways. g

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]

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" wrote in message
news
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" wrote:

On Mon, 24 Jan 2005 16:37:05 -0800, "Paul"
wrote:

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
Sideways. g

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]

On Tue, 25 Jan 2005 13:47:09 -0800, "Paul"
wrote:

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]