cant sort this query - 'Enter Parameter Value' error

illusionek

Registered User.
Local time
Yesterday, 20:03
Joined
Dec 31, 2013
Messages
92
Hi,

This drives me mad, I cannot sort below query in descending order by absolute value. If I do not use sort, all works fine but as soon as I try to sort by absolute value I get message to 'Enter Parameter Value'. I just cant understand why.

I tried to replace Abs([Variance (W2 - W1)]) with filed name AbsoluteValue and still the same result ;(

Can someone help?



Code:
SELECT [Query Union].[Stock Code] AS SKU, [Query Union].[Pallet No] AS [Pallet No], [Query Union].[Batch No#] AS Batch, IIf(IsNull([qW1 SOH].[Physical stock]),0,CDbl([qW1 SOH].[Physical stock])) AS [W1 Qty], IIf(IsNull([W2 SOH].[Good Stock]),0,CDbl([W2 SOH].[Good Stock])) AS [W2 Qty], [W2 Qty]-[W1 Qty] AS [Variance (W2 - W1)], Abs([Variance (W2 - W1)]) AS AbsoluteValue
FROM ([Query Union] LEFT JOIN [W2 SOH] ON ([Query Union].[Batch No#] = [W2 SOH].[Batch No#]) AND ([Query Union].[Pallet No] = [W2 SOH].[Pallet No]) AND ([Query Union].[Stock Code] = [W2 SOH].[Stock Code])) LEFT JOIN [qW1 SOH] ON ([Query Union].[Pallet No] = [qW1 SOH].[Pallet No Id]) AND ([Query Union].[Batch No#] = [qW1 SOH].[Batch number]) AND ([Query Union].[Stock Code] = [qW1 SOH].[Item number])
ORDER BY Abs([Variance (W2 - W1)]) DESC;
 
I don't think that you can use an alias in the order by try abs([qty w1]-[qty w2])

Brian

Just noticed that they are also aliases so you may need to go back further.
 
Many thanks for your help above.

Is there any workaround? If I type the full calculation without any aliases it works fine but then it is not really convenient especially that some calculations are really long without aliases.
 
No not really, I seem to remember that even stacked and sub queries don't solve the problem, only creating a temp table with the new values and then another query on that, ugh! That creates as many problems as it solves.

You would think that it would be possible but I don't know a solution.

Brian
 

Users who are viewing this thread

Back
Top Bottom