Query Field Using Other Fields in the Query

[Alan]

I have a query where I have two calculated fields using data from two
different tables. I want the query to also contain a field showing the sum
of these two calculated fields, but I cannot seem to make that work. From
Query design, I selected Builder, then the current query, then in the
expression box I typed:
[first calculated field]+[second calculated field]

When I ran the query, instead of calculating the expression, a parameter
input box opened asking for the first calculated field. Where am I going
wrong.

 

[Michel Walsh]

If you are using Jet, then you probably either have a typo for the
field/expression you are prompted, either you use a alias of an expression
in the where clause (or in the order by clause).



Vanderghast, Access MVP

 

[Michel Walsh]

Forgot to say what to do in the second case: replace the alias with the
expression it stands for:

NOT:


SELECT a+b AS c
FROM somewhere
ORDER BY c


BUT


SELECT a+b AS c
FROM somewhere
ORDER BY a+b



as example.



Vanderghast, Access MVP

If you are using Jet, then you probably either have a typo for the
field/expression you are prompted, either you use a alias of an expression
in the where clause (or in the order by clause).



Vanderghast, Access MVP

I have a query where I have two calculated fields using data from two
different tables. I want the query to also contain a field showing the
sum
of these two calculated fields, but I cannot seem to make that work.
From
Query design, I selected Builder, then the current query, then in the
expression box I typed:
[first calculated field]+[second calculated field]

When I ran the query, instead of calculating the expression, a parameter
input box opened asking for the first calculated field. Where am I going
wrong.

 

[KARL DEWEY]

Most of the time you can not use a calculated field in the same query where
it is calculated. The problem is that it is called before it is created.
Use the complete calculation again.