query: setting a condition on a calculated field

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!

Re: query: setting a condition on a calculated field

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

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

wrote in message
news:1192196823.086767.220280@v29g2000prd.googlegroups.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!
>

Re: query: setting a condition on a calculated field

myemail.an@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

Re: query: setting a condition on a calculated field

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

Re: query: setting a condition on a calculated field

On Oct 24, 6:24 am, "myemail...@googlemail.com"
wrote:
> > 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!

I have seen the behavior you describe in the past, but I don't
remember the circumstances. One way I've overcome the parameter is to
repeat the calculation in front of the criteria. Keep your calcluated
field the same, but try this in your criteria:
Field1 - Field2 <0

HTH,
Jana