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

  • Thread starter Thread starter John S. Ford, MD
  • Start date Start date
J

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
 
Indeed, you have to repeat the computed expression in the where clause

WHERE Function(tblD.Field4) = true


Vanderghast, Access MVP
 
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?
Click to expand...
.... or use a subquery.

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