Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    IIF() in criteria of query (XP/03)

    I must not be searching correctly, because based on what I found the following scenario should work, but doesn't.

    I am running a query where I use an IIF in the criteria line.
    SELECT myTable.pkey, myTable.CaseNum, myTable.OccurDate
    FROM myTable INNER JOIN sqMonthOccur ON myTable.pkey = sqMonthOcur.pkey
    WHERE (((myTable.OccurDate)=IIf(Month([Forms]![frm_myForm]![EndDate]) > 7,([myTable].[OccurDate])>#1/1/2004#,([myTable].[OccurDate])>#1/1/2005#)))
    ORDER BY myTable.OccurDate;

    If I take the criteria out, I get ALL records, as expected. But with the above WHERE I get no records returned. Is there something wrong with my Access, my query, or my mind?

    Thanks in advance.




    .

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIF() in criteria of query (XP/03)

    The criteria are not correct. As an example, let's assume that EndDate is 8/1/2006. The month is > 7, so the IIf function returns the value-if-true argument

    ([myTable].[OccurDate])>#1/1/2004#

    This is a boolean expression that evaluates either to True = -1 or to False = 0. So the criteria works out to

    WHERE myTable.OccurDate=-1 (or 0)

    Clearly, there will be no records satisfying this condition. Instead, try

    WHERE myTable.OccurDate > IIf(Month([Forms]![frm_myForm]![EndDate]) > 7, #1/1/2004#, #1/1/2005#)

    For the above example, this works out to

    WHERE myTable.OccurDate > #1/1/2004#

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: IIF() in criteria of query (XP/03)

    I do not think your expression is correct.

    Try putting this expression into a new calculated field and see what type of data it returns.

    newfield: IIf(Month([Forms]![frm_myForm]![EndDate]) > 7,([myTable].[OccurDate])>#1/1/2004#,([myTable].[OccurDate])>#1/1/2005#)

    You are asking the query to return records where myTable.OccurDate is equal to the above expression. Does the above expression actually return dates?

    It looks to me like that expression will return either true or false.
    Regards
    John



Posting Permissions

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