cant sort this query - 'Enter Parameter Value' error (1 Viewer)

illusionek

Registered User.
Local time
Today, 01:27
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;
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 08:27
Joined
Jul 9, 2003
Messages
16,245
Is absolute value a calculated field?
 

Brianwarnock

Retired
Local time
Today, 08:27
Joined
Jun 2, 2003
Messages
12,701
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.
 

illusionek

Registered User.
Local time
Today, 01:27
Joined
Dec 31, 2013
Messages
92
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.
 

Brianwarnock

Retired
Local time
Today, 08:27
Joined
Jun 2, 2003
Messages
12,701
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

Top Bottom