Referencing a "calculated" column

[Bob Howard]

Within a query, I created a column based on a fairly complex calculation
referencing a number of fields in the database and a number of fields in the
form that uses the query (this query is behind a ListBox on that form).
I've given the "created" column a name (let's call it XYZ). The result is
fine in that the content of that column in the ListBox is correct.

I now want to reference the contents of that column in a second "calculated"
column (further to the right, and beyond what appears in the ListBox). This
second column contains a couple of IIf 's and will return one of several
values ... depending on the value of XYZ in that row.

But when I run the query, the second column cannot "find" the first one ...
I always get a little panel asking me to enter a value for the XYZ column
(once per row in the recordsource).

How can I fix this (if I can). I would rather not repeat the entire
calculation (from XYZ) within the second column as that would make future
maintenance of the application more subject to error.

Thanks in advance...

bob

 

[Guest]

Access queries aren't too good at using a calculated column name in another
calculation. You might need to either repeat the calculation or build a query
based off the first query.

 

[Bob Howard]

I was afraid of that. I'll do the second thing in a higher-level query.
Thanks. Bob

 

[Michel Walsh]

You were using Jet or MS SQL Server as database? With MS SQL Server, there
is no much more to do than what Duane mentioned, but with Jet, if the
computation is only involved in the SELECT clause, in general, it is enough
to be sure that there is no other field with the same alias, AND that the
computed column, with the proper alias, is at the LEFT of any expression
using it:


SELECT qty*unitCost as basicCost, basicCost*stateTaxRate AS stateTax,
basicCost+stateTax AS costState, costState*federalTaxRate AS federalTax,
basicCost+stateTax+federalTax AS AmountToPay
FROM ...


Vanderghast, Access MVP