Prompting for values when I try to sort. Strange!

[prabhafriend]

SELECT IIf(IsNull([Beg_Qty]),0,[Beg_Qty]) AS Gen_Beg_Qty, IIf(IsNull([SumOfBeg_Qty]),0,[SumOfBeg_Qty]) AS BGI_Beg_Qty
FROM Result
ORDER BY [Gen_Beg_Qty]-[BGI_Beg_Qty] DESC;
Hi All!
I don’t know why this query prompts for the Fields [Gen_Beg_Qty] and [BGI_Beg_Qty]. I even tried to create an expression for that field using 'as' But no use. Work at Stake. Help Needed. Kindly explain the problem if possible.

[brian.naille]

I may be totally off base here - but I seem to remember having some issues trying to use aliases in an ORDER BY. Did you happen to try it without the ORDER BY?

On another note, if you have the Nz function available, those IIF statements can be written and read a little more easily as Nz([Beg_Qty], 0) - but that shouldn't have any bearing on the functionality of your query.

[prabhafriend]

Without the 'order by' it works fine. But I need to sort(order). Help needed.

[brian.naille]

OK, that's a step in the right direction. You should be able to use the actual function in the ORDER BY (I think). Try it this way:
SELECT IIf(IsNull([Beg_Qty]),0,[Beg_Qty]) AS Gen_Beg_Qty, IIf(IsNull([SumOfBeg_Qty]),0,[SumOfBeg_Qty]) AS BGI_Beg_Qty
FROM Result
ORDER BY IIf(IsNull([Beg_Qty]),0,[Beg_Qty]) - IIf(IsNull([SumOfBeg_Qty]),0,[SumOfBeg_Qty]) DESC;

Or also like this:
SELECT Nz([Beg_Qty]),0) AS Gen_Beg_Qty, Nz([SumOfBeg_Qty]),0) AS BGI_Beg_Qty
FROM Result
ORDER BY Nz([Beg_Qty]),0) - Nz([SumOfBeg_Qty]),0) DESC;

Like I said, the Nz doesn't change anything - it's just for readability. The IIFs tend to give me a headache. :-)

[brian.naille]

Oops - I left some extra parens in that second example. Should be:
SELECT Nz([Beg_Qty],0) AS Gen_Beg_Qty, Nz([SumOfBeg_Qty],0) AS BGI_Beg_Qty
FROM Result
ORDER BY Nz([Beg_Qty],0) - Nz([SumOfBeg_Qty],0) DESC;

[OBP]

Can I ask why do so many users show SQL when they have the problem, is it because they create the query in SQL rather than design mode?
You hardly ever get this kind of problem when using the query design wizard and "Design" view.
One of the problems to be wary of is the name of the actual field, sometimes Access represents it as Gen_Beg_Qty which may be [[Gen Beg Qty].

[brian.naille]

You present an interesting question which could be a forum topic of its own. I came from an Oracle background and am used to writing queries directly in SQL. So, the design mode screen at first felt very foreign to me. In many ways, if a SQL is well formatted, I can read it more quickly than the design view. However, the design view is growing on me and I use it for certain types of queries - especially those with a lot of WHERE clause conditions.

However, there are still things I use in queries which are harder to represent (and see visually) in the design mode - like inline views and nested queries.

So, I guess a lot of it comes down to personal preference. Your point, though, is well taken because the design view would certainly help solve these types of syntax issues.