Wrong sorting by query

[Geert Elias]

Hello all,

When making a new table by running a make table query based on a
table,
the field I added a descending sort, is not always sorted correct.
When I run the query 10 times, almost 7 times it's sorted correct and
3 times incorrect.

Anyone who can help me with this 'bug' ??

Thanks in advance.

[Wayne Morgan]

What are you basing the incorrect sort on, opening the resulting table and
looking at its data? If so, don't. There is no guarantee about the order
data is stored in a table. If you need it in a certain order, use a query to
retrieve the data and sort in that query.

--
Wayne Morgan
MS Access MVP
"Geert Elias" <ge*********@plastal.com> wrote in message
news:bb**************************@posting.google.c om...

Hello all,

When making a new table by running a make table query based on a
table,
the field I added a descending sort, is not always sorted correct.
When I run the query 10 times, almost 7 times it's sorted correct and
3 times incorrect.

Anyone who can help me with this 'bug' ??

Thanks in advance.

[Madhivanan]

Elias

If you are sorting the table by the column od char type which has
numbers, then you may get wrong sorting order. Can you post the result
you get?

Madhivanan

[Randy Harris]

"Madhivanan" <ma************@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Elias

If you are sorting the table by the column od char type which has
numbers, then you may get wrong sorting order. Can you post the result
you get?

Madhivanan

No. Wayne is correct. The OP is hoping to "pre-sort" his tables. He will
NEVER have complete control over the order in which records appear in a
table. If he wants the records to appear in some particular order, he MUST
use a query. (That's what they're there for)

--
Randy Harris
(tech at promail dot com)

[Geert Elias]

Indeed, I base my conclusion on the incorrect sorting on the result
table after running the query.

The column that has to be sorted in descending order is a DATE ( general
date ).
The problem is that I use this generated table as a base for a VBA
application that search data with the 'MoveNext' command (recordset).

Geert ELIAS

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Tim Marshall]

Geert Elias wrote:

The problem is that I use this generated table as a base for a VBA
application that search data with the 'MoveNext' command (recordset).

Then use a recordset based on the table.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Wayne Morgan]

Then open the recordset using a query that is based on the table. You can
either define the SQL of the query in the code or used a stored query. You
don't have to open the recordset directly on the table. You can sort as
desired in the query, this will cause your recordset to be sorted.

Example:
strSQL = "SELECT * FROM Table1 ORDER BY Table1.DateField;"
Set rst = CurrentDb.OpenRecordset(strSQL)

or
Set rst = CurrentDb.OpenRecordset("NameOfQuery")

Add other parameters as needed.

--
Wayne Morgan
MS Access MVP
"Geert Elias" <ge*********@plastal.com> wrote in message
news:42********@127.0.0.1...

Indeed, I base my conclusion on the incorrect sorting on the result
table after running the query.

The column that has to be sorted in descending order is a DATE ( general
date ).
The problem is that I use this generated table as a base for a VBA
application that search data with the 'MoveNext' command (recordset).

[Geert Elias]

Hello,

I rewrote the command so that my recordset is now based on a ( sorted )
query based on the table.
Now it works perfect !!!

Thanks to all for the quick response.

Kind regards

Geert ELIAS
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!