Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: WHERE (CASE WHEN THEN ELSE) in a query

    How do I express the where block in the title above? I have an SQL SELECT I am trying to convert to an MS Access query. One of the filters of the WHERE clause of my query has the following pattern (in bold):

    Code:
    WHERE
    CRITERIA_1
    | [AND CRITERIA_M]
    ...
    AND  (CASE WHEN TableA.ColumnX IS NULL 
                       THEN TABLEB.ColumnY
                       ELSE TABLEB_1.ColumnY
          END  = @MyVar)
    ...
    | [AND CRITERIA_N]
    Also it would be great if could point me out how to pass a variable to a Query.
    Any help greatly appreciated!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You can use the IIf() function in Access to replace the CASE. More info in Help. The most common way of passing a value to a query is using a form.
    Paul

  3. #3
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    g11db,

    You have to do that in VBA code, not the SQL query string.

    Paul, have I sent enough good weather over the mountain for your golf game? I've been working real hard on it.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by GolferGuy
    g11db,

    You have to do that in VBA code, not the SQL query string.

    Paul, have I sent enough good weather over the mountain for your golf game? I've been working real hard on it.
    It can be done in the SQL query itself with iif(), as pbaldy says.
    oh yeah... documentation... I have heard of that.

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

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Teddy, Thanks! Boy I missed that one. I have used IIF() in the Select clause, but sure did not see how it would work the way the CASE was written. I've played with that and now have learned something.
    Again, Thanks!

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You've done okay, Vic.

    Played both Saturday and Sunday, though if you could have cut down on the breeze it would have been nicer (it was cool enough that the breeze made it cold). Supposed to be in the 70's tomorrow, and I'm taking a tech out as a thank you for fixing my mom's laptop. Should be gorgeous!
    Paul

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Hit them long, straight, and seldom! My left knee has been acting up so I have not been abole to play in a couple of weeks. I'm planning on trying tomorrow though. Still waiting for the phone call when you are in the area.

  8. #8
    Join Date
    Jul 2006
    Posts
    111
    thanks guys! IIf(IsNull(),,) is what I was looking for. Enjoy your golf!

Posting Permissions

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