Results 1 to 4 of 4
  1. #1
    GAtkins is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    11

    Having Trouble With Nested IIF Stmt When Trying To Pick The Min of Three Values

    Forum members,

    I am having trouble with a nested IIF Statement when trying to select the minimum of three values.

    I have three possible values that I am trying to pick a buy target (BTTHREE) from. I have an "AvailToSpend" column, a "NeedToSpend column, and a "5PctFxd" column.

    I need an expression in another column to pick the minimum value from the three possible values.

    This expression is the closest I have come.

    BTTHREE: IIf([AvailToSpend]<=[NeedToSpend],IIf([AvailToSpend]<[5PctFxd],[AvailToSpend],[5PctFxd]),[5PctFxd])

    I have 96 rows returned by the particular query. The expression above returns the minimum value of the three in every case EXCEPT WHERE the "NeedToSpend" column is LESS THAN OR EQUAL TO the "AvailToSpend" column, in which case the expression returns the "5PctFxd" value. This occurs in 4 of the 96 records returned by the query.



    It seems like I have tried a million different ways to get this right and just can't seem to do it.

    Any help on that expression or an easier way to return the minimum of three values will be greatly appreciated. Thanks in advance for any help.

    Glenn

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    It's because of the = sign.

    Is 5PctFxd always less than NeedToSpend? Or vice versa? Or can either be lower than the other?

    Try:

    BTTHREE: IIf([AvailToSpend]<[NeedToSpend], IIf([AvailToSpend]<[5PctFxd],[AvailToSpend],[5PctFxd]), [NeedToSpend])
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    If any of the three can be smaller, then you need to make sure there are always two levels of comparison.
    Code:
    BTTHREE: IIF([AvailToSpend]<[NeedToSpend],
                 IIF([AvailToSpend]<[5PctFxd],[AvailToSpend],[5PctFxd]),
                 IIF([NeedToSpend]<[5PctFxd],[NeedToSpend],[5PctFxd]))
    Equal values can go either way and have the same results, so I don't code for <= in this situation.

  4. #4
    GAtkins is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    11
    Thank you very much! This the perfect solution because indeed any of the three can be smaller. I just couldn't quite get my head around the logic. Thanks so much again.

    Glenn

    Quote Originally Posted by Dal Jeanis View Post
    If any of the three can be smaller, then you need to make sure there are always two levels of comparison.
    Code:
    BTTHREE: IIF([AvailToSpend]<[NeedToSpend],
                 IIF([AvailToSpend]<[5PctFxd],[AvailToSpend],[5PctFxd]),
                 IIF([NeedToSpend]<[5PctFxd],[NeedToSpend],[5PctFxd]))
    Equal values can go either way and have the same results, so I don't code for <= in this situation.

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

Similar Threads

  1. Replies: 19
    Last Post: 11-07-2013, 11:10 AM
  2. Query to organize nested values
    By Seth Mannheim in forum Queries
    Replies: 11
    Last Post: 05-03-2013, 10:49 AM
  3. Replies: 3
    Last Post: 02-10-2013, 12:55 AM
  4. Trouble Pulling Values from the Internet
    By BallinWallin in forum Programming
    Replies: 10
    Last Post: 10-16-2011, 01:12 PM
  5. How to pick the n-th row from a query
    By Johnny C in forum Queries
    Replies: 2
    Last Post: 07-27-2010, 05:14 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