By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,462 Members | 2,308 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,462 IT Pros & Developers. It's quick & easy.

query: setting a condition on a calculated field

P: n/a
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!

Oct 12 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
With any numeric calculations, it is usually best to use the Null to Zero
Function Nz()

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

<my********@googlemail.comwrote in message
news:11**********************@v29g2000prd.googlegr oups.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!

Oct 13 '07 #2

P: n/a
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
Oct 13 '07 #3

P: n/a
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!
Oct 24 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.