Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    17

    Unanswered: Reference one column from another in a query...

    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

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You can't use alias names in the where clause. You'd have to go more this route:

    WHERE IIF((IIF(X=Y,"1","2"))="1","B","C") = "C"

    Seems to me it would be more efficient to write:

    WHERE IIF(X=Y,"1","2") = "1"
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2003
    Posts
    17
    It would be easier to do that, but I simplified my IIF statements for the post.

    I ended up making another query based off of that one that did the filtering. Ah well.

    -Ben

  4. #4
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    I ended up making another query based off of that one that did the filtering. Ah well.
    That's right a subquery can help sometimes when things get too complicated in one query...
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •