sorting data numerically in a table

[Kay Davies]

Hi there
I am hoping this is a really easy one. I am sorting table entries ascending
numberically. However I have got to 1000 and instead of following on from 999
it is placing them after the 100s I am sorting it via a querie and using MS
Access 2000. Many thanks.

 

[John Spencer]

Sounds as if your 'numbers' are text strings containing number characters. If
that is the case you will get a string sort which means that 1000 is after 100
and before 101 (as text).

You can try sorting by
Val(TheField)

If your field is a number field then you are probably doing something that
forces the data to be typed as a string.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Tim Johnson]

Are you sure that this field is storing the data as a number, and not as text?

This sounds indicative of text sorting, in which case you should:

1.) Back up your table (just in case something goes wrong)
2.) You can try converting the field in the table design from text to
number; you will be warned that you might lose data from this (I have not, as
of yet, but that's why you made the backup in the first place), or you can
create a new numeric field and run an update query that populates the new
field with data from the one you are trying to sort by.

Hope this helps,
Tim

 

[John W. Vinson]

Hi there
I am hoping this is a really easy one. I am sorting table entries ascending
numberically. However I have got to 1000 and instead of following on from 999
it is placing them after the 100s I am sorting it via a querie and using MS
Access 2000. Many thanks.

First off... you can't and don't sort Tables. Tables are unordered "bags" of
data. If you want to sort the records in your table use a Query.

Secondly, it sounds like you have these values in a Text type field. The text
string "999" sorts after the text string "1000", just as "ZZZ" sorts after
"BAAA" (hmmm... counting sheep leads to snoring??).

If there is a good reason to leave this field as Text, create a Query
containing the fields you want to see; in a vacant Field cell type

SortBy: Val([fieldname])

using the field containing these entries. Sort by this calculated field.

 

[Kay Davies]

Many thanks for this - all 3 replies basically said the same thing which
makes a lot of sense. However can I be really dense and ask where I type the
sort by value as it dosn't accept it in the sort by box
Many thanks again

 

[John W. Vinson]

Many thanks for this - all 3 replies basically said the same thing which
makes a lot of sense. However can I be really dense and ask where I type the
sort by value as it dosn't accept it in the sort by box
Many thanks again

Create a new Query based on your table.
In the first column of the query grid, type

SortBy: Val([fieldname])

into the Field cell (the top row of the grid) using the actual name of this
field (which we don't know because you didn't post it).

Uncheck the "Show" checkbox on this field.

Select "Ascending" in the sort row of the query grid.

Add any other table fields that you want to see.