How can I make a table stop auto sorting my info?

[AZLizzie2]

I am trying to link a combo box with the information in a table. The Table
contains numbers and I would like the combo box to stay in the order I enter
it into the table. However, as soon as I get to the number 10, the table
automatically moves the 10 under the 1 (ascending sort order). Is there a
way to change that? Thanks.

 

[Rick Brandt]

I am trying to link a combo box with the information in a table. The
Table contains numbers and I would like the combo box to stay in the
order I enter it into the table. However, as soon as I get to the
number 10, the table automatically moves the 10 under the 1
(ascending sort order). Is there a way to change that? Thanks.

Tables have no order. If you want a specific order you MUST use a query
with a sort order specified in the query. If you are already doing that the
sort you are seeing is because your numbers are actually text or are being
converted to text in the query. When sorted as text "10" does belong after
"1".

 

[peregenem]

Tables have no order. If you want a specific order you MUST use a query
with a sort order specified in the query.

Logically, tables have no order. However, under the covers a Access/Jet
database is just a file, a file has a physical order and result in a
table having a physical order.

So I can think of three ways of achieving the goal "to stay in the
order I enter it into the table" without using an explicit sort (such
as ORDER BY):

1. Create a table with no PK: the physical order will always be in
time-entered order regardless of compacting regime.

2. Create a table with an incrementing PRIMARY KEY (PK) e.g.

key_col DATETIME DEFAULT NOW() NOT NULL PRIMARY KEY
key_col INTEGER IDENTITY NOT NULL PRIMARY KEY

The physical order will be the PK order on file compact, however
because the PK increments and time is a continuum, the order will not
change.

3. Never compact the file.

 

[Rick Brandt]

Logically, tables have no order. However, under the covers a
Access/Jet database is just a file, a file has a physical order and
result in a table having a physical order.

So I can think of three ways of achieving the goal "to stay in the
order I enter it into the table" without using an explicit sort (such
as ORDER BY):

1. Create a table with no PK: the physical order will always be in
time-entered order regardless of compacting regime.

2. Create a table with an incrementing PRIMARY KEY (PK) e.g.

key_col DATETIME DEFAULT NOW() NOT NULL PRIMARY KEY
key_col INTEGER IDENTITY NOT NULL PRIMARY KEY

The physical order will be the PK order on file compact, however
because the PK increments and time is a continuum, the order will not
change.

3. Never compact the file.

Those are all based on assumptions that you cannot rely on. When data is
pulled with no explicit ORDER BY clause you can never be certain of the
order it will be presented.

 

[John Vinson]

So I can think of three ways of achieving the goal "to stay in the
order I enter it into the table" without using an explicit sort (such
as ORDER BY):

You're making probably invalid assumptions about the behavior of the
JET database engine. I've seen cases where the exact same table (with
no PK and no sort terms) is displayed in one order in table datasheet
view, and in a different order on a continuous subform. JET can and
will scramble the order, if you don't specify!

John W. Vinson[MVP]

 

[Ken Sheridan]

As Rick has pointed out the data type is clearly text. If you change the
data type to a long integer number then it will sort correctly. At present,
as text, you can sort on it by using the Val function to return its numeric
value and sort on that in the combo box's RowSource, e.g.

SELECT Myfield
FROM MyTable
ORDER BY VAL(Myfield);

The best way to guarantee that the rows are sorted in the order entered,
however, is to add a TimeStamp column to the table of date/time data type and
a default value of Now(). You never need to enter anything into this column
or ever see it, just use it for sorting:

SELECT Myfield
FROM MyTable
ORDER BY TimeStamp;

 

[peregenem]

You're making probably invalid assumptions about the behavior of the
JET database engine. I've seen cases where the exact same table (with
no PK and no sort terms) is displayed in one order in table datasheet
view, and in a different order on a continuous subform. JET can and
will scramble the order, if you don't specify!

With respect, unless you can post steps to reproduce, your evidence
remains anecdotal (without peer scrutiny, perhaps you missed
something...?)

 

[peregenem]

Those are all based on assumptions that you cannot rely on. When data is
pulled with no explicit ORDER BY clause you can never be certain of the
order it will be presented.

On the contrary, they are highly reliable. If you can show my
assumptions to be flawed, please post a demonstration.

Whether such assumptions provide a basis for a practical, workable
system is another matter entirely ...

 

[John Vinson]

With respect, unless you can post steps to reproduce, your evidence
remains anecdotal (without peer scrutiny, perhaps you missed
something...?)

I yield to your superior experience. At any rate, I don't think it's
urgent to spend time creating a database to try to force this behavior
(which I have seen, but not investigated since there is a simple and
effective solution to it).

John W. Vinson[MVP]

 

[peregenem]

I yield to your superior experience. At any rate, I don't think it's
urgent to spend time creating a database to try to force this behavior
(which I have seen, but not investigated since there is a simple and
effective solution to it).

BTW I didn't mean to sound arrogant. I _am_ looking for the black swan
to falsify that 'All swans are white', I just haven't been convinced of
its sighting yet.