Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2005
    Posts
    196

    Unanswered: IIF statement in Query Criteria

    Hi,

    I have set the following criteria in a query field:

    Code:
    IIf(>=[StartDate]=Null,Null,>=[StartDate])
    As you can probably see I want to say that if there are no results greater than or equal to start date, take the criteria as null, else use criteria as greater than or equal to start date. However, it's not producing the results that I intend - is this the right way of going about it? StartDate is a field elsewhere in the query.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Fuerteventura
    if there are no results greater than or equal to start date
    No results of what?
    Code:
    IIf(>=[StartDate]=Null,Null,>=[StartDate])
    This highlighted is incorrect syntax
    Code:
    Iif(<Results> >= [Start Date], Null, [Start Date])
    You need something more like this
    George
    Home | Blog

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Access doesn't like when you have "Null" as a possible result. Very often, when Access sees that, it will give you Null as a result no matter whether the condition of the IIf statement is fulfilled or not. You want something a bit different. Something like

    Code:
    IIf(MyResult >= [StartDate], MyResult, [StartDate])
    If you want to discard StartDate also, change to

    Code:
    IIf(MyResult >= [StartDate], MyResult, " ")
    But for Heaven's sake, don't use Null.

    Sam

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    My question is, if there are no records that are greater than the StartDate what do you want to return? All the records or none of the records?

    There is probably a better way to approach the problem. I don't believe an IIF will work the way you are trying to gte it to work.

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by Sam Landy
    Access doesn't like when you have "Null" as a possible result.
    To be a little more specific, a criteria tells Access to both select rows for which the criteria is true and exclude rows for which the criteria is false.

    So what is Access supposed to do with rows for which the criteria is null? Null is neither true nor false so Access doesn't know what to do. People often interpret null as meaning "I don't know," so by that logic you're telling the database "I don't know" whether you should include a row.

    You're making this whole thing way more complicated than it needs to be by using Iif. There's a time and place for iif, and it's generally not when you have to do boolean logic. My reasoning here may be off because I don't have your actual SQL query to test, but hopefully it points you in the right direction.

    As you can probably see I want to say that if there are no results greater than or equal to start date, take the criteria as null, else use criteria as greater than or equal to start date.
    First, you need a subquery to see if there are no results >= start date. That means:

    NOT EXISTS (SELECT * FROM YourTable WHERE YourColumn >= [Start Date])

    You can put this inside a criteria.

    Second, let's rephrase "take criteria as null" to mean ignore the criteria.

    To rephrase your criteria: "if not exists (blah blah) then ignore this criteria, else return >= start date?"

    Now, a more sensible way to do this would be:

    if exists (blah blah) then return >= start date.

    We can just throw out the whole "else ignore this."

    If then statements are known as implication in logic. If a then b is often written as a -> b, or a implies b.

    Access doesn't have an "implies" operator, but a implies b is logically equivalent to not a or b.

    You can do or statements by simply putting two criteria in separate rows.

    So the first row should be not exists (blah blah), and the second should be >= [start date].

  6. #6
    Join Date
    Aug 2005
    Posts
    196
    Thanks to all for your help and my apologies for the delay in acknowledging.

Posting Permissions

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