Select field as a from tbl where a=... Won't work?

[bad-aries]

Hi all,
In Access 2007, I tried to execute a query like this

Select field as a from tbl where a="123"

Access returned a popup box a asked me what is "a".

However, if I do something like,

Select field from tbl where field ="123"

and it works. Isn't this specific to Access? If so, how could I fix this because I have some complex calculation like

Select tPOItems.Quantity, Sum(tDelivered.DQuantity) AS Delivered, tPOItems.Quantity-Delivered AS Remaining

and I would like to set the query do not display anything with Remaining is Zero.

Can someone please help me?

Thanks.

 

[namliam]

This is not specific to Access, this is SQL... You cannot put constraints (where) on aliased columns. Instead you need to put them on the calculation like you did with the a vs field in your example...
Thus not: Remaining = 0
But: tPOItems.Quantity-Delivered = 0