Zero comes before 8000 in a descending sort?!?!?

[Phil]

I have a table with Five fields. I give it to you here in CDF format.

"Category","item_types_name","brand_name","customer_id","qty"
"TrucksBullet","Trucks","Bullet","1441",8518
"TrucksBullet","Trucks","Bullet","1505",4650
"TrucksBullet","Trucks","Bullet","1607",0
"TrucksBullet","Trucks","Bullet","8851",4308

I have a query that sorts it by Qty, in descending Order.

SELECT t1.Category, t1.item_types_name, t1.brand_name, t1.customer_id,
t1.SumOfship_qty
FROM t1
ORDER BY t1.Category, t1.qty DESC;

In the universe I live in, the results should fall in the following order:

8518,4650,4306,0

In the Universe of Bill Gates, however, the results are:

0,8518,4650,4306

As you can see, Category is the same for all fields, so that field sort
is irrelevant. The only relevant field sort is Qty.

What the heck? Access 2002 sp3

This comes from a larger dataset, but I did indeed cut the table down to
these four records, and get these results. In the large dataset, I
tried excluding Zero's, and a (-1) which was in the dataset showed up in
the number one position instead.

Phil

 

[Tom Ellison]

Dear Phil:

A theory. Could it be that the datatype is not a numeric one, but text?
And that the zero is not a zero but the letter "O"?

Why do we have letters that look like digits anyway, like O and l instead of
0 and 1? Can you see the difference between 1 and l? Stupid!!! And then,
in handwriting, they taught me to write a 2 for the letter Q!!! No kidding!
And then my math teacher tells me to put a little horizontal line through
the middle of my 7. What's that about? Next, my drafting teacher teaches
me 6 and 9 so they are the same, but upside down! So what happens if
someone is looking at the page upside down? Is that 90. or .06? Did you
find my pet peave, or what! Why even have a language if it's so ambiguous
that it doesn't mean anything?

I've seen this before, and it would explain the problem.

Or, not!

Tom Ellison

 

[[MVP] S.Clark]

If I had to guess, I'd say you(or Bill Gates surreptitiously taking control
of your machine) sorted by the first three columns, after the query was
executed, or it was pulled in by the table of which the query is based.

Open the query in design view, and view the Query properties. Examine the
Order By property. It overrides the ORDER BY clause when viewing the query.

And... after you blank this out, maybe apologize to Bill and his Universe.

8-D

 

[Phil]

Nope. That is why I dumped it as a CDF. IF it is text, it gets
enclosed in quotes.

 

[Allen Browne]

What is t1?
Is it an attached table, or in this database?
If attached, what kind? Other Access mdb? Excel spreadsheet?

Open t1 in design view.
What is in the Data Type column beside the Qty field?
Text? Number?

If Number, when you select this row, what is in the Size property (lower
pane of table design window)?

If the Size is Decimal, see:
Incorrect Sorting (Decimal fields)
at:
http://allenbrowne.com/bug-08.html

 

[Phil]

t1 is the access Database table reproduced in it's entirety in that CDF.
QTY is indeed a NUmber field, of size "decimal."

I changed it to size "double", and guess what?
My query works just fine.
I don't think this is the first time you have saved my sanity.

Thank you very much.

 

[Phil]

If you had to guess, you would guess wrong. Read the entry from Allen
Browne. He nailed my problem.

As far as apologizing to Bill Gates and his universe, For what? Either
his (company's) gross incompetence or complete lack of care and concern
for their customers resulted in probably many many people spending
cumulatively massive amounts of time trying to make thier queries
function properly. I amnot so concerned that the bug exists, as much as
it has not been fixed.

According to the KB article Allen pointed me to (837148,) this problem
occurs in access 2000, 2002, and 2003, and Microsoft has been aware of
this bug since at least Mar 2004. I have access 2002 all patched up,
and the problem is still there, two years after they knew about it.
They are not bothering to fix it, most likely because they arrogantly
think they have no duty to provide software, that they charge quit a bit
of money for, that is actually functional.

Billyboy needs to apologize to ME and all of his Access customers for
selling us defective software, and not having the common courtesy to fix
it when he knows it is broken. This is far from the first time this
sort of thing has screwed me up, making me waste time and effort.

Phil