Results 1 to 7 of 7
  1. #1
    Goodge12 is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2008
    Posts
    25

    Nested Iif statement help

    Hi All-

    Can anyone help me with this gem:

    LostSell-in: IIf(([Actual Lead Time by Item]![Avg Actual Supplier Lead Time Days]-[Actual Lead Time by Item]![Avg Planned Supplier Lead Time Days])<=0,0,Iif(([Actual Lead Time by Item]![Avg Actual Supplier Lead Time Days]-[Actual Lead Time by Item]![Avg Planned Supplier Lead Time Days])<=1,(([R12 Receipts by Item]![Receipts]*([Actual Lead Time by Item]![Avg Actual Supplier Lead Time Days]-[Actual Lead Time by Item]![Avg Planned Supplier Lead Time Days]))*(100-[Actual Lead Time by Item]![On Time Ship %]*0.01)*255))),Iif(([Actual Lead Time by Item]![Avg Actual Supplier Lead Time Days]-[Actual Lead Time by Item]![Avg Planned Supplier Lead Time Days])>=7,(([R12 Receipts by Item]![Receipts]/(7))*(100-[Actual Lead Time by Item]![On Time Ship %]*0.01)*255)),(([R12 Receipts by Item]![Receipts]/([Actual Lead Time by Item]![Avg Actual Supplier Lead Time Days]-[Actual Lead Time by Item]![Avg Planned Supplier Lead Time Days]))*(100-[Actual Lead Time by Item]![On Time Ship %]*0.01)*255)

    I keep getting the error that I'm missing a bracket or pantesis, but I can't find it anywhere - I've counted and there are 18 open, 18 closed....



    Thanks!

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    try to change [x]![y] to [x].[y]

  3. #3
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Following is revised, can you find out where I changed?

    LostSell-in: IIf(([Actual Lead Time by Item]![Avg Actual Supplier Lead Time Days]-[Actual Lead Time by Item]![Avg Planned Supplier Lead Time Days])<=0,0,Iif(([Actual Lead Time by Item]![Avg Actual Supplier Lead Time Days]-[Actual Lead Time by Item]![Avg Planned Supplier Lead Time Days])<=1,(([R12 Receipts by Item]![Receipts]*([Actual Lead Time by Item]![Avg Actual Supplier Lead Time Days]-[Actual Lead Time by Item]![Avg Planned Supplier Lead Time Days]))*(100-[Actual Lead Time by Item]![On Time Ship %]*0.01)*255)),Iif(([Actual Lead Time by Item]![Avg Actual Supplier Lead Time Days]-[Actual Lead Time by Item]![Avg Planned Supplier Lead Time Days])>=7,(([R12 Receipts by Item]![Receipts]/(7))*(100-[Actual Lead Time by Item]![On Time Ship %]*0.01)*255)),(([R12 Receipts by Item]![Receipts]/([Actual Lead Time by Item]![Avg Actual Supplier Lead Time Days]-[Actual Lead Time by Item]![Avg Planned Supplier Lead Time Days]))*(100-[Actual Lead Time by Item]![On Time Ship %]*0.01)*255))

  4. #4
    Goodge12 is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2008
    Posts
    25
    Now it says that I have entered a function containing the wrong number of arguments

  5. #5
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Quote Originally Posted by Goodge12 View Post
    Now it says that I have entered a function containing the wrong number of arguments
    I made it worse

  6. #6
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    this must work:
    LostSell-in: IIf(([Actual Lead Time by Item]![Avg Actual Supplier Lead Time Days]-[Actual Lead Time by Item]![Avg Planned Supplier Lead Time Days])<=0,0,Iif(([Actual Lead Time by Item]![Avg Actual Supplier Lead Time Days]-[Actual Lead Time by Item]![Avg Planned Supplier Lead Time Days])<=1,(([R12 Receipts by Item]![Receipts]*([Actual Lead Time by Item]![Avg Actual Supplier Lead Time Days]-[Actual Lead Time by Item]![Avg Planned Supplier Lead Time Days]))*(100-[Actual Lead Time by Item]![On Time Ship %]*0.01)*255),Iif(([Actual Lead Time by Item]![Avg Actual Supplier Lead Time Days]-[Actual Lead Time by Item]![Avg Planned Supplier Lead Time Days])>=7,(([R12 Receipts by Item]![Receipts]/(7))*(100-[Actual Lead Time by Item]![On Time Ship %]*0.01)*255),(([R12 Receipts by Item]![Receipts]/([Actual Lead Time by Item]![Avg Actual Supplier Lead Time Days]-[Actual Lead Time by Item]![Avg Planned Supplier Lead Time Days]))*(100-[Actual Lead Time by Item]![On Time Ship %]*0.01)*255))))

  7. #7
    Goodge12 is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2008
    Posts
    25
    Thanks, this solved the parenthesis issue... but now when I try to run, field names are popping up like they exist in the tables. Ugh - this thing is a nightmare!

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

Similar Threads

  1. Nested IIF statements?
    By laavista in forum Access
    Replies: 9
    Last Post: 06-22-2010, 10:35 AM
  2. Nested If Expressions
    By Lynn in forum Forms
    Replies: 5
    Last Post: 03-25-2010, 10:11 AM
  3. nested if statement with two criteria
    By kendra in forum Queries
    Replies: 5
    Last Post: 06-16-2009, 04:07 PM
  4. Nested Joins
    By zephaneas in forum Programming
    Replies: 0
    Last Post: 11-10-2008, 11:49 AM
  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