Results 1 to 8 of 8
  1. #1
    Bellablue is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Oct 2011
    Posts
    5

    Nested If statement

    I am trying to use the nested if statement and got this error message

    ""you may have entered a comma without a preceding value or identifier"



    Here's the query:

    Revised Cost: IIf((AND([Total Cost]![EXP_CAT]="PAYROLL”, [Total Cost]![EXP_TYPE]="A” )),0,(iif((AND([Total Cost]![EXP_CAT]="PAYROLL”, [Total Cost]![EXP_TYPE]="B”)),0,[Total Cost]![SumOfACT])))

    I have a table "Total Cost", if Exp_Cat=Payroll and "Exp_Type"=A or B, set the revised cost to "0", otherwise set cost ="SumofAct".

    What went wrong? Please help. Thanks!
    Last edited by Bellablue; 10-07-2011 at 10:45 PM. Reason: Delete

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,515
    You appear to be using the Excel syntax for AND rather than the Access syntax. It would be

    IIf([Total Cost]![EXP_CAT]="PAYROLL” AND [Total Cost]![EXP_TYPE]="A",True, False)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Bellablue is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Oct 2011
    Posts
    5
    Thanks, but how do I get the "B" part in there then?

    I want if Payroll+A show up together, Revised cost=0, and if Payroll+B show up together, Rivised cost also=0, Otherwise Rivesed cost="Sumofact"

  4. #4
    Bellablue is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Oct 2011
    Posts
    5
    I tried this, but the error message is "The expression you entered has a function containing the wrong number of arguments"

    IP NP EXP: iif(([Total Cost]![EXP_CAT] ="PAYROLL" AND( [Total Cost]![EXP_TYPE] ="A" or "B"),0, [Total Cost]![SumOfACT] ))

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,515
    You seem to have an extra set of parentheses there, and the proper syntax for the or is:

    [Total Cost]![EXP_TYPE] ="A" or [Total Cost]![EXP_TYPE] = "B"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Bellablue is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Oct 2011
    Posts
    5
    I tried it but still not working. Do I need to repeat the "Payroll" portion twice?

    IIf(([Total Cost]!EXP_CAT="PAYROLL" And [Total Cost]!EXP_TYPE="A"), or [Total Cost]!EXP_TYPE="B”) 0,[Total Cost]!SumOfACT))

    Can you please write the whole thing instead of just the middle part because that way I will get the whole picture.

    Thanks!

  7. #7
    Bellablue is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Oct 2011
    Posts
    5
    I finally got it to work

    IIf(([Total Cost]!EXP_CAT="PAYROLL" And [Total Cost]!EXP_TYPE="A"),0,IIf([Total Cost]!EXP_CAT="PAYROLL" And [Total Cost]!EXP_TYPE="B",0,[Total Cost]!SumOfACT))

    Thanks for your help PBaldy!

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,515
    Sorry, I'm on the road. Glad you got it working. This might also have worked, if I have the logic right:

    IIf([Total Cost]!EXP_CAT="PAYROLL" And ([Total Cost]!EXP_TYPE="A" or [Total Cost]!EXP_TYPE="B”), 0,[Total Cost]!SumOfACT)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 7
    Last Post: 08-17-2011, 01:49 PM
  2. Nested IIf Statement
    By traquino98 in forum Queries
    Replies: 5
    Last Post: 06-11-2011, 10:56 AM
  3. Nested Iif statement help
    By Goodge12 in forum Queries
    Replies: 6
    Last Post: 09-21-2010, 11:45 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