Referring to Calculated Fields in a Select Query. 1

[BNPMike]

This query works:

SELECT MAS.[CAC-BUY-TO-LET-IND] AS Mval, Summit.[CAC-BUY-TO-LET-IND]
FROM MAS LEFT JOIN Summit ON MAS.[CAC-LOAN-ID] = Summit.[CAC-LOAN-ID];

However when I try to compare the two fields Access doesn't understand; it asks for a parameter value:

SELECT MAS.[CAC-BUY-TO-LET-IND] AS Mval, Summit.[CAC-BUY-TO-LET-IND]
FROM MAS LEFT JOIN Summit ON MAS.[CAC-LOAN-ID] = Summit.[CAC-LOAN-ID]
WHERE (((Summit.[CAC-BUY-TO-LET-IND])=[Mval]));

This is basic but just can't remember how you do it and I can't see anything in Access 2003 Inside Out.

 

[Golom]

Try

SELECT MAS.[CAC-BUY-TO-LET-IND] AS Mval, Summit.[CAC-BUY-TO-LET-IND]
FROM MAS LEFT JOIN Summit ON MAS.[CAC-LOAN-ID] = Summit.[CAC-LOAN-ID]
WHERE (((Summit.[CAC-BUY-TO-LET-IND])= [red]MAS.[CAC-BUY-TO-LET-IND][/red] ));

although, since you are doing a JOIN anyway

SELECT MAS.[CAC-BUY-TO-LET-IND] AS Mval, Summit.[CAC-BUY-TO-LET-IND]

FROM MAS LEFT JOIN Summit 

ON MAS.[CAC-LOAN-ID] = Summit.[CAC-LOAN-ID]
   AND Summit.[CAC-BUY-TO-LET-IND] = MAS.[CAC-BUY-TO-LET-IND]

SQL evaluates clauses in approximately this order

FROM
WHERE
GROUP BY
ORDER BY
SELECT

so it doesn't know that [Mval] is a field alias when it is evaluating the WHERE clause.