Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2009
    Posts
    11

    Unanswered: IIF Statement in Where clause

    I have a very old query that our company has had in place for years upon years. It is not a good query, but it doesnt' matter as it runs at 4am when nothing else runs, so performance is not an issue. The query is what drives a report that prints labels ever morning for our warehouse to put on boxes. So, no user input, just straight data and printing.

    However, I am asked to add another clause to this query, but I do not know how to do it.

    Basically, I want this:

    DateRecd = IIF([LineSerial] Is Not Null,Is Not Null)

    That obviously doesn't work, but I'll explain what I'm trying to accomplish.

    If the LineSerial field is not blank, then the DateRecd field must have some value in order to return results. If the LineSerial field is blank, then disregard any criteria with the DateRecd field.

    How can I do this in straight query form?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not sure to understand. Why don't you use a simple WHERE clause?
    Code:
    WHERE [LineSerial] Is Not Null
    Anyway, IIf() expects 3 parameters:
    Code:
    IIf(<Conditional expression>, <Value when True>, <Value when False>)
    While there are only two in your example. Moreover you cannot change a value from a table in a SELECT query:
    Code:
    DateRecd = IIF([LineSerial] Is Not Null,Is Not Null)
    Have a nice day!

  3. #3
    Join Date
    Oct 2009
    Posts
    11
    I need it to be this in the where clause

    if the Line Serial has a value, then the where clause needs to read this:

    WHERE (LineSerial Is Not Null) AND (DateRecd Is Not Null) AND (Status = 0)

    If the line serial does not have a value, then it needs to read this:

    WHERE (Status = 0)

    This was supposed to read:

    WHERE (DateRecd = IIF([LineSerial] Is Not Null,Is Not Null))

    I know it expects 2, which is why I said this statement will not work, but I am looking for something similar.

  4. #4
    Join Date
    Oct 2009
    Posts
    11
    anyone have any ideas?

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If LineSerial field is not blank, then the DateRecd field must have some value in order to return results. If the LineSerial field is blank, , then disregard any criteria with the DateRecd field.
    It should be something like:
    Code:
    WHERE (LineSerial Is Not Null AND DateRecd = <SomeValue>) OR (LineSerial Is Null)
    Have a nice day!

  6. #6
    Join Date
    Oct 2009
    Posts
    11
    I think I need to tweak it a bit to something like this:

    WHERE (LineSerial Is Not Null AND DateRecd Is Not Null AND Status = 0) OR (LineSerial Is Null AND Status = 0)

    It is as simple as an or clause, duh me.

    Now, however, I have to incorporate this into a monster query, so I'll go play with that. Thanks!

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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