Avatar of Rex
RexFlag for United States of America asked on

My query is not sorting properly

I have a query that is supposed to sort the issues by qty in descending order. It seems to do that for quite a few rows, but then I notice some fields that are out of sequence, like a 75 after a 16.

Can anyone show me the problem in my SQL? I am pasting it here. I am pasting the function (TransformTextFields) in the code area below

SELECT (KO_QN_Data.Field20) AS Defect, Sum(Clng(TransformTextFields([KO_QN_Data.DefectQty (ext)]))) AS QTY
FROM KO_QN_Data
WHERE GetWeekEndingDate([Created on])>=Date()-90 AND KO_QN_Data.[Name 1]="KO-Borden" AND GetBucket([Code group])="Product"
GROUP BY Clng(TransformTextFields([KO_QN_Data.DefectQty (ext)])), (KO_QN_Data.Field20)
ORDER BY Clng(TransformTextFields([KO_QN_Data.DefectQty (ext)])) DESC;

Thanks for any help.
Public Function TransformTextFields(TheSum As Variant) As Integer
 
If IsNull(TheSum) = True Then
    TransformTextFields = 0
   Else
    TransformTextFields = CInt(Val(TheSum))
End If

Open in new window

Microsoft AccessSQL

Avatar of undefined
Last Comment
Rex

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
nexusnation

Rex85:
This exact query was posted due to other syntax problems at http:Q_23439219.html.

The clauses of a SQL statement are not simply thrown together.  Any answer you get here will very affect/impact your solution in the other question, and vice versa.

I suggest that experts venture over to http:Q_23439219.html, and not post here.  Otherwise, this will be nightmarish, with two different groups of experts hacking away at the same SQL statement.
ASKER
Rex

Sorry. I thought the error and the sorting were two different issues, so I made them two questions.

Rex
SOLUTION
Mark Wills

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Rex

Sorry for the delay. I have been out on another project. I appreciate the help.
Your help has saved me hundreds of hours of internet surfing.
fblack61