IIf Statments in queries

Posted on 2007-10-11
Last Modified: 2010-03-20

Can someone help me with the formatting of a line of my query. I am trying to say;
if column[7] is null then check column [19], if they are both null return FALSE.
if column[7] is not null check its value is greater than 0, if it is not greater than 0, check column [19] value is greater than 0, if it is also not greater than 0 return FALSE, but if either column [7] or [19] return a value greater than 0 return TRUE.

I have tried to do this in a query, and I need to keep it in this format.

dox_fsa: IIf(IsNull([7]),IIf(IsNull[19],False,IIf([7]>0,True,IIF[19]>0,True,False))

Something like that?

Question by:andyb7901
    LVL 4

    Expert Comment

    use the NZ() function instead of IsNull in queries

    it works better for me...
    LVL 119

    Accepted Solution

    try this

    dox_fsa: IIf([7] is null and [19] is null,False,IIf([7]>0 Or [F19]>0,True,False))
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    dox_fsa: IIf(IsNull([7]),IIf(IsNull[19],False, IIf([19]>0,True,False)),IIf([7]>0,True,IIf(isNull([19]),False,IIf([19]>0,True,False)))

      Think I got enough parends on the end.


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now