Results 1 to 6 of 6
  1. #1
    traquino98 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Posts
    4

    Question Nested IIf Statement

    I made a database many years ago for my business. I built it just after taking a class on Access, so it was all fairly simple for me. Now, over 9 years later, I have to make a simple change to an If statement and I can't seem to get it right. I use it to calculate my commission percentage based on my current pay plan, which was recently changed. Here is my trusty old statement:
    Code:
    Commission: IIf([Sale Date]<=37726,([Finance Log]!Reserve+[Finance Log]!CL+[Finance Log]!AH+[Finance Log]!GAP+[Finance Log]!VSA+[Finance Log]!TDR+[Finance Log]!Perma)*0.2,([Finance Log]!Reserve*0.14)+(([Finance Log]!CL+[Finance Log]!AH+[Finance Log]!GAP+[Finance Log]!VSA+[Finance Log]!TDR)*0.17)+([Finance Log]!Perma*0.2)+([Finance Log]!TAC*0.17)+([Finance Log]!Tire*0.17))
    Attempting to keep all the old information, and add one more criteria, I tried nesting a second If statement in, to look like this:
    Code:
    Commission: IIf([Sale Date]<=37726,([Finance Log]!Reserve+[Finance Log]!CL+[Finance Log]!AH+[Finance Log]!GAP+[Finance Log]!VSA+[Finance Log]!TDR+[Finance Log]!Perma)*0.2,IIf([Sale Date]>=40695,(([Finance Log]!Reserve+[Finance Log]!GAP)*0.14)+(([Finance Log]!CL+[Finance Log]!AH+[Finance Log]!TDR)*0.17)+(([Finance Log]!Perma+[Finance Log]!VSA)*0.2)+([Finance Log]!TAC*0.17)+([Finance Log]!Tire*0.17)+([Finance Log]!Setup*0.10),([Finance Log]!Reserve*0.14)+(([Finance Log]!CL+[Finance Log]!AH+[Finance Log]!GAP+[Finance Log]!VSA+[Finance Log]!TDR)*0.17)+([Finance Log]!Perma*0.2)+([Finance Log]!TAC*0.17)+([Finance Log]!Tire*0.17)+([Finance Log]!Setup*0.10))
    The problem is, the results that are returned, even for deals after June 1, are still showing the old calculation. How can I fix this?

  2. #2
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    To make it easier to troubleshoot, would you provide values for the following:

    -------------------
    [Finance Log]!AH:
    [Finance Log]!CL:
    [Finance Log]!GAP:
    [Finance Log]!Perma:
    [Finance Log]!Reserve:
    [Finance Log]!Setup:
    [Finance Log]!TAC:
    [Finance Log]!TDR:
    [Finance Log]!Tire:
    [Finance Log]!VSA:
    -------------------

    Also, for the above values, what would the commissions be:

    before 4/15/2003 (37726):

    between 4/15/2003 (37726) and 6/1/2011(40695):

    after 6/1/2011(40695):

    Thanks,

  3. #3
    traquino98 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Posts
    4
    For the deal in questions, it is:

    -------------------
    [Finance Log]!AH: 0
    [Finance Log]!CL: 0
    [Finance Log]!GAP: 0
    [Finance Log]!Perma: 508.95
    [Finance Log]!Reserve: 200
    [Finance Log]!Setup: 0
    [Finance Log]!TAC: 290
    [Finance Log]!TDR: 170
    [Finance Log]!Tire: 0
    [Finance Log]!VSA: 1200
    -------------------

    And the percentages:

    before 4/15/2003 (37726): 20% on everything

    between 4/15/2003 (37726) and 6/1/2011(40695): 14% on Reserve, 20% on Permas and 17% on everything else

    after 6/1/2011(40695): 14% on Reserve and GAP, 20% on Permas and VSA, and 17% on everything else.

    So with my current programming, which I though was correct, the date on the deal is 6/6/11, so it should return the latest values, totaling $447.99, but instead it returns the value for everything before 6/1/11, which is $411.99.

    Thanks

  4. #4
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am using A2K3..

    I created a table with your fields and entered the data you provided. Then I created a query (qryComm), added the fields from the table and pasted in the 2nd formula from your first post in a blank column. I don't like spaces in field names, so I removed the space in "Sale Date". In my example, the field is "SaleDate".

    I created a form (Form3) using qryComm as the record source.

    Looking at the results (see jpg), it appears to return the results you want. I didn't have to change anything in your formula. Maybe I am missing something.....????

    Attached is the mdb......
    Last edited by ssanfu; 06-10-2011 at 11:15 PM. Reason: spelling

  5. #5
    traquino98 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Posts
    4
    I'm not sure what the difference is, but mine is not returning my desired results. The calculation is part of a query, that pulls from two separate tables (Finance Log and Sales Log). Would that make a difference?

  6. #6
    traquino98 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Posts
    4
    Well, I'm honestly not sure what I did, but I was able to fix it. I switched the criteria around to follow more of a timeline instead of bouncing all over, ran the query, adjusted the sale date three times to make sure it worked properly, and then saved it all. Thanks for the help.

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

Similar Threads

  1. Nested IIF problems
    By Brian Collins in forum Access
    Replies: 2
    Last Post: 10-12-2010, 01:37 PM
  2. Nested Iif statement help
    By Goodge12 in forum Queries
    Replies: 6
    Last Post: 09-21-2010, 11:45 AM
  3. Nested If Expressions
    By Lynn in forum Forms
    Replies: 5
    Last Post: 03-25-2010, 10:11 AM
  4. nested if statement with two criteria
    By kendra in forum Queries
    Replies: 5
    Last Post: 06-16-2009, 04:07 PM
  5. Replies: 0
    Last Post: 05-17-2008, 01:18 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