Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2003
    Posts
    280

    Unanswered: IIF statement in the criteria

    Well, I'm trying to put two true values in one IF statement but I'm getting an error.

    example:

    IIF([Forms]![ExpenseForm]![cboPlantation]="-1"), -1) <---Work
    IIF([Forms]![ExpenseForm]![cboPlantation]="-1"), -1 And -2) <---Doesn't Work

    IIF([Forms]![ExpenseForm]![cboPlantation]="-1"), -1) And IIF([Forms]![ExpenseForm]![cboPlantation]="-1"), -2) <---Doesn't Work

    This is in the criteria of a query. Anyways, do y'all have any ideas to make it work?

    Thanks in advance..

    Don

  2. #2
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Maybe try this? Could be bogus, but worth a try?

    IIF([Forms]![ExpenseForm]![cboPlantation]="-1"), -1,((IIF([Forms]![ExpenseForm]![cboPlantation]="-1"), -2),)

  3. #3
    Join Date
    Apr 2003
    Posts
    280
    From just looking at it, the comma after the number -1 is consider ELSE in the if statement. I'm looking for ways to put the value -1 and -2 in one if statement..

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What result set are you trying to achieve? You have a few funny looking syntax errors:

    For starters:

    IIF([Forms]![ExpenseForm]![cboPlantation]="-1"), -1)

    shouldn't work. You're closing the statement after ="-1". The ", -1)" shouldn't be recognized as part of the declaration, and should error out on a wrong number of arguments exception. Simply put, you opened one set of paranthesis but you closed two.

    The same is true for this statement:

    IIF([Forms]![ExpenseForm]![cboPlantation]="-1"), -1) And IIF([Forms]![ExpenseForm]![cboPlantation]="-1"), -2)

    Are you trying to return a value based on whether cboPlantion = -1 OR -2? AND doesn't make sense, whereas it will always return false since a combo box can only have one value at any given time. Assuming you are looking for the OR statement, try this:

    IIF([Forms]![ExpenseForm]![cboPlantation]="-1" OR "-2", valueiftrue, valueiffalse)

    Or are you trying to return a string containing -1 and -2? In that case:

    IIF([Forms]![ExpenseForm]![cboPlantation]="-1", "-1 AND -2", "")

    Essentially the way you presented the question, you are trying to assign two values to the same field at the same time. This is physically impossible. Maybe if you psoted a sample result set we could be a bit more concise?
    Last edited by Teddy; 01-14-04 at 13:06.

  5. #5
    Join Date
    Apr 2003
    Posts
    280
    Originally posted by Teddy
    What result set are you trying to achieve? You have a few funny looking syntax errors:

    For starters:

    IIF([Forms]![ExpenseForm]![cboPlantation]="-1"), -1)

    shouldn't work. You're closing the statement after ="-1". The ", -1)" shouldn't be recognized as part of the declaration, and should error out on a wrong number of arguments exception. Simply put, you opened one set of paranthesis but you closed two.

    The same is true for this statement:

    IIF([Forms]![ExpenseForm]![cboPlantation]="-1"), -1) And IIF([Forms]![ExpenseForm]![cboPlantation]="-1"), -2)

    Are you trying to return a value based on whether cboPlantion = -1 OR -2? AND doesn't make sense, whereas it will always return false since a combo box can only have one value at any given time. Assuming you are looking for the OR statement, try this:

    IIF([Forms]![ExpenseForm]![cboPlantation]="-1" OR "-2", valueiftrue, valueiffalse)

    Or are you trying to return a string containing -1 and -2? In that case:

    IIF([Forms]![ExpenseForm]![cboPlantation]="-1", "-1 AND -2", "")

    Essentially the way you presented the question, you are trying to assign two values to the same field at the same time. This is physically impossible. Maybe if you psoted a sample result set we could be a bit more concise?
    Oops! I forgot to put another paranthesis. I just type it up while looking at the other monitor, I didn't went back to check what I type up..

    Anyways, I'm pulling the value from a combo box. The value from the combo box is a string not an number data type.

    IIF([Forms]![ExpenseForm]![cboPlantation]="-1" OR "-2", valueiftrue, valueiffalse)

    --Ok, for this, I just need to check if it's a string of -1, if it true then -1 and -2 would be query.

    Or are you trying to return a string containing -1 and -2? In that case:

    IIF([Forms]![ExpenseForm]![cboPlantation]="-1", "-1 AND -2", "")

    --I just try this, it won't work...

    Sample Data...
    FacilityName, TotalAmount, FacilityID, Plantation, Revenue
    Plantation, 2342, 485, -2, 0

    The above data is just the plantation with the string -2. The reason that I want to do the code mention above because this query will be UNION with another query, so I need to query the specific plantation out... I hope this is understand, if not ask me..Thanks..

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by lansing
    Code:
    Sample Data...
    FacilityName,       TotalAmount,      FacilityID,      Plantation,    Revenue
    Plantation,              2342,                485,               -2,              0
    Uh.. I still don't understand where the -1 comes in.. what you just posted would be the result set of:

    IIF([Forms]![ExpenseForm]![cboPlantation]="-1", "-2",""). Again, you can only have one value assigned to a field at any given time. If you need to assign both values, you may need to assign the string "-1-2" and parse it for the query LEFT(Plantation, 2) = "-1" and RIGHT(plantation, 2) = "-2"
    Last edited by Teddy; 01-14-04 at 13:29.

  7. #7
    Join Date
    Apr 2003
    Posts
    280
    Originally posted by Teddy
    Uh.. I still don't understand where the -1 comes in.. what you just posted would be the result set of:

    IIF([Forms]![ExpenseForm]![cboPlantation]="-1", "-2",""). Again, you can only have one value assigned to a field at any given time. If you need to assign both values, you may need to assign the string "-1-2" and parse it for the query LEFT(Plantation, 2) = "-1" and RIGHT(plantation, 2) = "-2"
    Well, you ask me for a sample result, so I just query for one of them. Sorry for the confusion if it was my fault.

    Anyways, here is some more of the data.

    Sample Data...
    FacilityName, TotalAmount, FacilityID, Plantation, Revenue
    Plantation, 2342, 485, -2, 0
    Acadian, 2323, 234, -1, 0
    EastHaven, 5933, 254, 0, 232
    Woodland, 2342, 534, -1, 235
    Bethany, 3946, 464, 3, 534

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it can't work the way you want it to.

    instead, try;

    iif(something = "-1", trueResult, iif(something = "-2", trueResult, falseResult))

    which translates to a regular if like this
    if something = "-1" then
    trueResult
    else
    if something = "-2" then
    trueResult
    else
    falseResult
    endif
    endif

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •