query: setting a condition on a calculated field

[myemail.an]

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.

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?

Thanks!

[Phil Stanton]

With any numeric calculations, it is usually best to use the Null to Zero
Function Nz()

Try MyCalculation = Nz(Field1) - Nz(Field2)
Phil

<my********@goo glemail.comwrot e in message
news:11******** **************@ v29g2000prd.goo glegroups.com.. .

>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.

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?

Thanks!

[Rick Brandt]

my********@goog lemail.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

[myemail.an]

In the query designer just entering "<0" in the criteria row underneath your
calculated field should work fine.

It does not. If I try it, when I run the query I get a small window
asking me for the value of MyCalculation ("Enter parameter value").
The same happens if I try to sort the calculated field (while in
design view).
I tried this a number of times in a number of differente queries and
databases, and the result has always been the same. Is it possible
that Access lets you neither set conditions nor sort calculated
fields?

Thanks!