Unanswered: Reference one column from another in a query...
I'm trying to reference a named column in the formula of another column within one query.
SELECT IIF(X=Y,"1","2") AS Column1, IIF([Column1]="1","B","C") AS Column2
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?