Use of calculated field in "WHERE" part of SQL statement?

[studiosa]

Dear Microsoft Gurus,
I am teaching myself Microsoft Access 2007. So, please forgive me if this question is very naive.

I tried the following two queries that I expected to give the same answer (pull out the rows with both [Amount Paid] and [Current Due] set to zero). The first of these two queries worked as expected. The second query Asks me for [Total Amount] instead of calculating it.

Does this mean that calculated fields cannot be used in an SQL query in the "Where" part of the SQL statement, or am I simply doing it wrong in Query 2?

Query 1 (works as expected):
SELECT [Client Number], [Client Name], [Amount Paid], [Current Due], [Amount Paid] + [Current Due] AS [Total Amount]
FROM Client_table
Where ([Amount Paid] =0) AND ( [Current Due] = 0 )
;

Query 2 ( Asks me for [Total Amount] instead of calculating it)

SELECT [Client Number], [Client Name], [Amount Paid], [Current Due], [Amount Paid] + [Current Due] AS [Total Amount]
FROM Client_table
Where [Total Amount]= 0
;

[alansidman]

Change your SQL statement to read:

Please Login or Register  to view this content.

You have to spell out the expression as Access does not understand the alias as a field name unless it is used in a subsequent query with this one as the record source.

[studiosa]

Hi Alan,
That's great! Thank you very much. I truly appreciate your quick, clear repliues that not only answer my query, but also teach me "the next step".

Studiosa.