Solved Ordering a Custom String as a Number (1 Viewer)

[Weekleyba]

I have query that was working fine until the GrandTotal number went beyond 100.
The query is meant to sort projects by their CustomNumberForOrdering but, at the same time showing the decreasing Balance and increasing RunTotal for each project.
SQL expression for CustomNumberForOrdering is: Format([GrandTotal],"Standard") & Format([PROJECTID],"00000") AS CustomNumberForOrdering
I believe is this is no longer a number but a read as text. (maybe?)
Thus, when 100 is reached, it puts this right after the number 10 since it reads it left to right. (maybe?)

So all that to say, it is not ordering the way I want it to. I want the largest CustomNumberForOrdering on top, and then descend from there.
Can anyone help?

Here's the queries SQL:

SELECT A.CustomNumberForOrdering, CCur([A].[MajorM&IFundTotal]+[A].[BEMARFund]-Sum([B].[TotalPoolRequest])) AS Balance, CCur(Sum([B].[TotalPoolRequest])) AS RunTotal, Count(B.ProjectID) AS [Counter], [A].[MajorM&IFundTotal]+[A].[BEMARFund] AS ALLFUNDS, A.ProjectTitle, A.CostEstimate, A.GrandTotal, A.FYID
FROM [BALANCE_Q 1] AS A INNER JOIN [BALANCE_Q 1] AS B ON A.CustomNumberForOrdering<=B.CustomNumberForOrdering
GROUP BY A.GrandTotal, A.CustomNumberForOrdering, [A].[MajorM&IFundTotal]+[A].[BEMARFund], A.ProjectTitle, A.CostEstimate, A.FYID
HAVING (((A.FYID)=[Forms]![Main_F]![cboFY]))
ORDER BY Count(B.ProjectID);

 

[Pat Hartman]

Strings sort character by character, left to right so 100 is less than 20 and comes between 10 and 11. So yur problem is that you are sorting on a string and assuming it will sort as a number. It won't. one possible solution is to separate the CustomNumberForOrdering into two separate fields and define BOTH of them as long integer. If you want to print leading zeros, you will need to format the right part of the sring to insert them:

Format(fld2, "0000000")

The other alternative is to make the left part fixed width. Decide how big the number needs to be and add as many leading zeros as you need to similar to the way the right part is formatted with leading zeros.

 

[Weekleyba]

Thanks Pat!! I was stuck on that. I used the alternative and in 3 seconds the problem is solved.
Appreciate your time.

 

[Pat Hartman]

Not the way I would prefer but as long as it works for you