Can't use a calculated field in the same SQL code's WHERE clause

[John S. Ford, MD]

I've created a query using a SQL statement that draws a number of fields
from different tables (tblA.Field1, tblB.Field2, tblC.Field3, etc. I've
also created an additional (calculated) field using the following in the
query's Select statement:

Funtion(tblD.Field4) AS CalculatedField

The problem is, SQL doesn't recognize this new field when I include it in
the WHERE statement:

WHERE CalculatedField=True

So is it true that you can't use a field in the WHERE statement if it was
created in the same SQL code? Is there a way of getting around this?

John

 

[Michel Walsh]

Indeed, you have to repeat the computed expression in the where clause

WHERE Function(tblD.Field4) = true


Vanderghast, Access MVP

 

[Bob Barrows]

I've created a query using a SQL statement that draws a number of
fields from different tables (tblA.Field1, tblB.Field2, tblC.Field3,
etc. I've also created an additional (calculated) field using the
following in the query's Select statement:

Funtion(tblD.Field4) AS CalculatedField

The problem is, SQL doesn't recognize this new field when I include
it in the WHERE statement:

WHERE CalculatedField=True

So is it true that you can't use a field in the WHERE statement if it
was created in the same SQL code? Is there a way of getting around
this?

.... or use a subquery.

Select ... FROM (
Select ..., Funtion(tblD.Field4) AS CalculatedField
FROM table) as q
WHERE CalculatedField=True

 

[John S. Ford, MD]

Thank you both! Both suggestions are very helpful.

John