473,320 Members | 1,969 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

"ORDER BY" negtive decimal values bug hasn't been fixed

Hello.
I read the Microsoft article about the sorting of negative deciaml
values (http://support.microsoft.com/?id=837148), however, the problem
wasn't solved.
It works fine when I use a simple query:
SELECT Costs
FROM MyTable
ORDER BY Costs DESC

But when I add a criteria to the query, the results are in the wrong
order again:
SELECT Costs
FROM MyTable
WHERE [Quarter]=1
ORDER BY Costs DESC

Any suggestions?
Thanks.

Feb 10 '06 #1
4 1526
Correct: AFAIK, this bug has never been fixed, despite the fact that it is
now more than 5 years old.

It's pretty embarrassing that Access gets such an incredibly simple query
wrong.

It's even more embarassing that Microsoft doesn't care enough about Access
to fix such a basic flaw.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ha*****@bezeqint.net> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Hello.
I read the Microsoft article about the sorting of negative deciaml
values (http://support.microsoft.com/?id=837148), however, the problem
wasn't solved.
It works fine when I use a simple query:
SELECT Costs
FROM MyTable
ORDER BY Costs DESC

But when I add a criteria to the query, the results are in the wrong
order again:
SELECT Costs
FROM MyTable
WHERE [Quarter]=1
ORDER BY Costs DESC

Any suggestions?
Thanks

Feb 10 '06 #2
<ha*****@bezeqint.net> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Hello.
I read the Microsoft article about the sorting of negative deciaml
values (http://support.microsoft.com/?id=837148), however, the problem
wasn't solved.
It works fine when I use a simple query:
SELECT Costs
FROM MyTable
ORDER BY Costs DESC

But when I add a criteria to the query, the results are in the wrong
order again:
SELECT Costs
FROM MyTable
WHERE [Quarter]=1
ORDER BY Costs DESC

Any suggestions?
Thanks.

What about the obvious? Change the field type. Do you really have a field
named 'costs' where the decimal type is more appropriate than the currency
type? If you think so, let us know your reasons.
Feb 10 '06 #3
If I indexed Costs and still got wrong Desc Sorts
OTTOMH I might try any of four things:

a) using a FLOAT or CURRENCY
b) moving to MSDE
c) sorting on (Costs ^ 3) Desc (has worked properly in a few tests)
d) opening an ADODB Recordset and sorting it on Costs Desc (has worked
properly in a few tests) as below:
Sub temp3()
Dim r As ADODB.Recordset
Set r = New ADODB.Recordset
With r
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.ActiveConnection = CurrentProject.AccessConnection
.Open "SELECT * FROM Table2 WHERE ID > 0"
.Sort = "DecimalValues DESC"
While Not .EOF
Debug.Print .Collect(0), .Collect(1)
.MoveNext
Wend
End With
End Sub

Feb 10 '06 #4
Thanks.
The field type change did the trick. 'Costs' was just an example. I
used a different numeric field instead.

As Allen wrote, it is embarassing that Microsoft treats Access users
this way.

Feb 10 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

23
by: ian justice | last post by:
Before i post actual code, as i need a speedyish reply. Can i first ask if anyone knows off the top of their head, if there is a likely obvious cause to the following problem. For the moment i've...
1
by: Simon Wigzell | last post by:
Is it possible to retrieve data with ASP sorted on 2 fields? I've tried : SELECT * FROM ORDER By Field1 AND Field 2 But it gives an error. I've searched the internet on this topic to no avail....
2
by: John Morris | last post by:
I'm running a simple DBCC DBREINDEX ('myTable') and I receive the following error: "Server: Msg 169, Level 15, State 2, Line 2 A column has been specified more than once in the order by list....
3
by: Ptbrady | last post by:
"Order By" fails in form for linked table. -------------- My A2K database has worked well for several years, but now has been split into front-end/back-end and has the following problem. I have a...
9
by: Frederik | last post by:
Hi all, I'm building a C# application that uses a M$ Acces database. In one of the queries I use something like the following: SELECT id FROM mytable WHERE id IN (20, 12, 21, 14) The result...
17
by: G Fernandes | last post by:
Can someone explain what is meant by "directory order" in the questoin for K and R 2 exercise 5-16? I can't seem to find a solution for this exercise on the main site where clc goers have posted...
7
by: Giles | last post by:
An ASP page outputs data from the query "Select ThisAndThat from comments WHERE pageURL='" & pageURL & "' ORDER BY threadID, datesent" (Access mdb) threadID is a string (OK, I know!), which means...
2
by: Richard Hollenbeck | last post by:
I have the following query in a combo box: SELECT courses.courseCode, courses.courseDescription FROM courses ORDER BY courseDescription; I want to change the "ORDER BY" to display in...
3
by: slinky | last post by:
I have a main form from which I lauch a small form in a fixed location that still allows the main form to appear behind it. This works fine. What I want to do is launch another small form in...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.