Results 1 to 4 of 4
  1. #1
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58

    IIF condition in a query

    Hi all,

    I am trying to write an IIF statement in a query to get the result as shown in the attached excel.
    Please somebody help!!!!!

    TIA,
    Mismag


    Attached Thumbnails Attached Thumbnails Penetration.png  

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Code:
    IIF(
        ProductName = "Gap",
        [Gap Deals] / [Service contract delas],
        IIF(
            ProductName = "Lease Protect",
            [Lease protect Deals] / [Lease Deals],
            Deals / TotalDeals
        )
    )
    It's unclear given the data presented what [Gap Deals], [Lease Protect Deals], [Service Contract Deals], and [Lease Deals] are.

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Oh wait, I think I understand now. Try this (replace TABLE_NAME with the name of your table):

    Code:
    IIF(
        ProductName = "Gap",
        Deals / DLookup("Deals", "TABLE_NAME", "ProductName='Service Contracts'"),
        IIF(
            ProductName = "Lease Protect",
            Deals /  DLookup("Deals", "TABLE_NAME", "ProductName='Leased'"),
            Deals / TotalDeals
        )
    )

  4. #4
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58
    Quote Originally Posted by kd2017 View Post
    Oh wait, I think I understand now. Try this (replace TABLE_NAME with the name of your table):

    Code:
    IIF(
        ProductName = "Gap",
        Deals / DLookup("Deals", "TABLE_NAME", "ProductName='Service Contracts'"),
        IIF(
            ProductName = "Lease Protect",
            Deals /  DLookup("Deals", "TABLE_NAME", "ProductName='Leased'"),
            Deals / TotalDeals
        )
    )
    This formula doesn't throw any errors but the Gap and Lease Protect Penetration % values are not calculating correctly.


    Please see the attachment.

    Any help is greatly appreciated.





    TIA,
    Mismag
    Attached Thumbnails Attached Thumbnails Penetration_New.png  

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

Similar Threads

  1. Possible to use query is LIKE condition?
    By ctakacs in forum Access
    Replies: 3
    Last Post: 01-06-2021, 06:34 AM
  2. Query help to write a condition
    By Dgalaxy in forum Access
    Replies: 6
    Last Post: 06-03-2020, 11:34 AM
  3. Check box in query condition: is this possible?
    By WesHarding in forum Queries
    Replies: 7
    Last Post: 03-22-2017, 11:30 AM
  4. Need help with condition in a query
    By NightWalker in forum Queries
    Replies: 1
    Last Post: 09-30-2016, 01:18 PM
  5. Add Query Based on if Condition
    By shakeelmscw in forum Queries
    Replies: 17
    Last Post: 01-18-2013, 02:35 AM

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