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:
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:
Code:
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);