my********@googlemail.com wrote:
I use Access 2007 and create queries with a number of calculated
fields/expressions (I'm still a novice so please forgive me if my
wording is imprecise...), like: MyCalculation = Field1 - Field2.
Well, that IS imprecise ;-) In the query designer it would be written in an
empty colum as...
MyCalculation: Field1 - Field2
....while in SQL it would be written as...
Field1 - Field2 AS MyCalculation
However, I noticed that I cannot query the database setting a
condition on this newly created field (eg <0); if I do, when I run the
query , Access shows me an input box asking for the value of
MyCalculation. The only solution I managed to find is to create yet
another query and set the filter there, but this seems a rather clumsy
and inefficient way to me.
Is there a better way?
In the query designer just entering "<0" in the criteria row underneath your
calculated field should work fine. In SQL you would however not refer to the
name you gave the calculated field. You would repeat the expression like...
SELECT Field1
Field2
Field1 - Field2 AS MyCalculation
FROM SomeTable
WHERE Field1 - Field2 < 0
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com