troubleshooting Question

My query is not sorting properly

Avatar of Rex
RexFlag for United States of America asked on
Microsoft AccessSQL
5 Comments1 Solution324 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros