Results 1 to 5 of 5
  1. #1
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110

    Expresssion IIF

    Can someone please tell me what I have done wrong to not be able to get this to work. I'm used to doing nested iff staements in Excel, but not access.



    IIf(([DateUpdated])IsNull,iif([GoogleSearchVerifiedDate] isNull OR [DirectorInsolvencyInfoReceivedDate] IsNull, "Incomplete",iif([GoogleSearchVerifiedDate] IsNull OR [DirectorInsolvencyInfoReceivedDate] IsNull, "Incomplete","Complete"),"Complete")

    Regards

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Is Null is a sql term. WHERE someField Is Null, or conversely WHERE someField Is Not Null
    IsNull is a vba function and must precede the test and the test be in parentheses: IsNull(someThing)

    Instead of ugly IIFs you might be better off with something else; perhaps a Select Case block of code or Switch function or Choose function. Can't tell where you're using that expression so hard to say what the options are.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    try not to nest IIFs. Instead, make a lookup table and the result of that should give you the result needed w/o lots of IIFs.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a pattern for nested iif's that works. Perhaps, you can adjust your set up to follow this pattern. But there may be better, less confusing constructs - but we need to know more about the requirement.

    Code:
    IIf([First Quarter Grade]<60,0
      ,IIf([first quarter Grade]<70,0
       ,IIf([First Quarter Grade]<80,1 
        ,IIf([First Quarter Grade]<90,2,3 )
       )
      )
     )
    Last edited by orange; 03-12-2021 at 12:27 PM. Reason: adjustments

  5. #5
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110
    I just went with this and it works well: IIf(IsNull([DateUpdated]) Or IsNull([DirectorInsolvencVerifiedby]) Or IsNull([GoogleSearchVerifiedDate]),"Incomplete","Complete")

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums