calculations within queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting a rather complicated query to produce many different
calculated values based on each other, most of them work but two of the
calculations are asking for values when I run the query as if they were
parameterized which they are not.

The first is: fields A and B and the field C:Nz([A],0)/(,0) and then
when I run the query it asks me what C is. This is a problem since I use C in
other calculated fields later on and it is supposed to be pulling from the
database.
 
Kathryn said:
I am attempting a rather complicated query to produce many different
calculated values based on each other, most of them work but two of
the calculations are asking for values when I run the query as if
they were parameterized which they are not.

The first is: fields A and B and the field C:Nz([A],0)/(,0) and
then when I run the query it asks me what C is. This is a problem
since I use C in other calculated fields later on and it is supposed
to be pulling from the database.
Click to expand...


You can't use an aliased field name in other expressions in the same query.

Either...
Create a second query to feed the first on einto where you WILL be able to
refer to [C] in expressions.

or...
In your current query replace places where you have entered "[C]" with the
same expression that [C] is aliasing.

Instead of...
SomeAlias: [C] + [SomeOtherField]

use...
SomeAlias: (Nz([A],0)/(,0)) + [SomeOtherField]
 
C is an alias and you cannot use it in calculations in the Query.

You need to repeat the same expression instead of C in your "later"
calculations.

Actually, we don't know which one is the earlier calculation and which one
is the later calculation in JET. We think of calculate C first and then use
C in later calculations. However, JET may not "think" the same way. Hence,
you cannot use the alias C.
 
In addition to Rick and Van's response:
Nz([A],0) / (,0)
Click to expand...

What is [A] being divided by?? Did you omit a 2nd Nz?:
Nz([A],0) / Nz(,0)
and even then, what about division-by-zero errors if is 0 or Null?

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Kathryn said:
I am attempting a rather complicated query to produce many different
calculated values based on each other, most of them work but two of the
calculations are asking for values when I run the query as if they were
parameterized which they are not.

The first is: fields A and B and the field C:Nz([A],0)/(,0) and then
when I run the query it asks me what C is. This is a problem since I use C
in
other calculated fields later on and it is supposed to be pulling from the
database.
Click to expand...
 
Back
Top