Hey All,

I'm trying to reference a named column in the formula of another column within one query.

i.e.

SELECT IIF(X=Y,"1","2") AS Column1, IIF([Column1]="1","B","C") AS Column2
FROM WHEREVER



But it ONLY works if I don't try to filter Column2. If I add a criteria to it, the WHERE clause cannot refer to Column1. This following line, for example, would not work. It would cause Access to prompt me for Column1.

WHERE IIF([Column1]="1","B","C") = "C"


My actual code is much more complicated and I do not want to nest too many IIFs or repeat my code too often, any way to accomplish what I'm after?

Thanks,

Ben