Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1052
  • Last Modified:

IIf statement

I am trying to run an iif statement like the following and for some reason the last part with the and does not work the formula passes as correct and will run but will not effect the outcome.  This is an update statement in access


IIf([table1!field1] Is Null,"test1",
IIf([table1!field2] Is Null,"test2",
IIf([table1!field1] Is Null And [table2!field2] Is Null,"testing123")))
0
H-SC
Asked:
H-SC
  • 8
  • 7
  • 5
  • +6
8 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I'd create a separate function for this, and refer to it in the query.

Code:
------
Public function MyTest(var1 as whatever, var2 as whatever) as string

if Nz(var1, "") = "" then
   if Nz(var2, "") = "" then
       'Both are null
       MyTest = "testing123"
   else
       'var1 is null, var2 is not.
      MyTest = "test1"
   end if
else
   if Nz(var2, "") = "" then
       'var1 is not null, var2 is null
       MyTest = "test2"
   else
       'Both are not null
      MyTest = "think of something to put here"
   end if
end if

end function

In your query:
---------------
MyValue = MyTest([table1!field1], [table2!field2] )

Hope this helps.
-Jim
0
 
flavoCommented:


IIf([table1!field1] Is Null,"test1",
IIf([table1!field2] Is Null,"test2",
IIf([table1!field1] Is Null, Iif([table2!field2] Is Null,"testing123")))

Dave
0
 
wblakelyCommented:
H-SC,

Your statement has some missing brackets but I'm assuming that's just a typo here or it wouldn't have run at all.  Your innermost iif statement is missing a return value if it evaluates to false.  I would change it to this:

IIf([table1]![field1] Is Null,"test1",IIf([table1]![field2] Is Null,"test2",IIf([table1]![field1] Is Null And [table1]![field2] Is Null,"testing123","?")))

The question mark in the statement above is the missing return value.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Steve BinkCommented:
Your logic is short-circuiting.  If both Field1 and Field2 are NULL, the very first IIF will return "test1".  Since the other IIF statements are in the False section of the first IIF, they will never be evaluated.  In the event Field1 is not NULL, the second IIF will pick up Field2 as NULL, and return "test2", ignoring the rest.  If neither of them are NULL, both of the first IIF statements fail through to the False section.  The last IIF will evaluate as False and return nothing.  Try reversing the logic to pick the most specific category first:

IIF(((Field1 IS NULL) AND (Field2 IS NULL)), "testboth", IIF(Field1 IS NULL, "test1", IIF(Field2 IS NULL, "test2)))

Or alternatively, a variation of Jim's:

If ((field1 IS NULL) and (field2 IS NULL)) then
    'both NULL
elseif (field1 IS NULL) then
    ' field1 is NULL
elseif (field2 IS NULL) then
    ' field2 is NULL
end if

Jim used the NZ function instead of a check for NULL, which I would normally recommend also.  If you want to consider a blank value the same as a NULL value, make that change.
0
 
NestorioCommented:
I think 3rd part never will execute, because the null condition for both fields is trapped before.
0
 
aikimarkCommented:
try:

IIf(IsNull([table1!field1]) ,"test1",
IIf(IsNull([table1!field2]) ,"test2",
IIf(IsNull([table1!field1]) And IsNull([table2!field2]) ,"testing123")))
0
 
GreymanMSCCommented:
As routinet  pointed out, if field1 is null the result will always be "test1" whether or not field2 is null!
 
Just reverse it

IIf([table1!field1] Is Null And [table2!field2] Is Null,"testing123",
IIf([table1!field2] Is Null,"test2",
IIf([table1!field1] Is Null,"test1","test0")))
0
 
aikimarkCommented:
Since IIF() is a VB/VBA construct, I think the problem is the use of the SQL "IS NULL" condition check rather than the IsNull() VB/VBA function as part of the IIF() parameters.
0
 
NestorioCommented:
Try this:

IIf(IsNull([table1!field1]) and IsNull([table2!field2]), "testing123", IIf(IsNull([table1!field1], "test1", IIf(IsNull([table1!field2], "test2")))
0
 
GreymanMSCCommented:
Nestorio, the [square] braces need to go around each of the table name and the field name, not just around both of them.  Plus, don't foget the fourth posibility: when neither is null.

IIf(IsNull([table1]![field1]) and IsNull([table2]![field2]), "testing123", IIf(IsNull([table1]![field1], "test1", IIf(IsNull([table1]![field2], "test2", "test3")))
0
 
JonoBBCommented:
Access has a much better way of doing all of this - using the Switch function, rather than nexted iff's.

Check it out.
0
 
aikimarkCommented:
also check out the NZ() function in MSAccess to translate Null values.

You might be able to get your desired results with something like
Select ... NZ([table1].[field1], "test1") As TF1, NZ([table2].[field2], "test2") As TF2 ...

IIf(TF1 = "test1" and TF2 = "test2", "testing123")
0
 
NestorioCommented:
Thanks Greyman, you're right:

Please JonoBB, could you explain the use of the Switch function?
0
 
JonoBBCommented:
Well, switch allows you to do what a nested iif statement does, just a bit more simpler

for example

IIF function:
iif(Test1,Result1, iif(Test2, Result2, iif(Test3, Result3, Result4)))

This can be translated into a switch function like this:
=switch(Test1, Result1, Test2, Result2, Test3, Result3, Test4, Result4)

Hope this helps.

 
0
 
NestorioCommented:
OK, I see, it's a clearer sintax than IIf in this case. Thanks.
0
 
JonoBBCommented:
To be honest, its not really necessary in your case as you dont have THAT many iif statements, but it is nice to know for when the syntax gets a bit hairy
0
 
aikimarkCommented:
and when you upgrade this application to MSDE or SQL Server you can use the CASE construct in your SELECT statement in both dynamic SQL and in stored procedures.
0
 
Steve BinkCommented:
So we have 3 pages of us commenting, but no clue if anything worked yet...  LOL

To summarize:

1) Logic needs to be reversed
2) IsNull() vs "IS NULL" vs NZ()    (BTW, any of those should work since this is an SQL statement and not strictly VB...I still prefer NZ)
3) Other forms of the same using a sub/function or Switch (didn't include CASE because we're not in MSSQL yet..)

I guess we were all bored yesterday, eh?  hehehe
0
 
H-SCAuthor Commented:
Everyone,

Wow!!!  This is great.  Many many thanks for the overwhelming response.  I am working on a couple of different projects and just haven't had a chance to try any of these out yet, but hope to sometime today.
0
 
aikimarkCommented:
H-SC,

Depending on your UPDATE requirements, you might consider eliminating your IIF() or SWITCH() function altogether.

No-IIF alternative 1:
Execute multiple different UPDATE statements, with different updating values and WHERE clause criteria:
Example:
Update Utable Inner Join Table1 On ...
Set Utable.UField = "test1"
Where Table1.test1 Is Null;
Update Utable Inner Join Table1 On ...
Set Utable.UField = "test2"
Where Table1.test1 Is Not Null and Table1.test2 Is Null;
Update Utable Inner Join Table1 On ...
Set Utable.UField = "testing123"
Where Table1.test1 Is Not Null and Table1.test2 Is Not Null;

No-IIF alternative 2:
Create a Union query for Table1 with multiple different Select statements supplying values for the field to supply an updating value.  Use similar logic to the Where clauses in the SQL example above.

Join the Utable to the union query in a single Update statement.
0
 
H-SCAuthor Commented:
All,

I went ahead and ran this statement (one that i found in past code) that I used and it seemed to work.
Does this look ok ????

IIf([Table_1!Field_1] Is Not Null And [Table_1!Field_2] Is Not Null And [Table_1!Field_3] Is Not Null,"This Is A Test 1",
IIf([Table_1!Field_1] Is Null And [Table_1!Field_2] Is Null And [Table_1!Field_3] Is Null,"This Is A Test 2",
IIf([Table_1!Field_1] Is Null,"This Is A Test 3",
IIf([Table_1!Field_2] Is Null,"This Is A Test 4",IIf([Table_1!Field_3] Is Null,"This Is A Test 5")))))
0
 
aikimarkCommented:
H-SC,

>>Does this look ok ????

1. The answer depends on what you want to do.  As per my prior comment, I'm surprised that the "Is Null" (SQL) works within an IIF() function.  It would help us experts greatly if you stated what behavior/result you need.

2. Where does this IIF() function reside?  In interpreted your initial comment, "This is an update statement in access", to indicate that this was executing within an UPDATE query.  Is this a correct assumption on my part?  If not a correct assumption, such as a this being a macro invoked as part of a form event, then my answer will be VERY different.  What exactly is being updated?

3. The use of the 'bang' notation [Table_1!Field_1] might also refer to a control on a form that is bound to column Field_1 (if you used the wizard to create the form).  Is this the case?
0
 
H-SCAuthor Commented:
aikimark,

1 - the result is simply to display a statement into the table (a field for the resulting statement) where all of the data resides that the compare on the iif is on.  So basiclly all it will do is meet the true condition and display a message.

2 - this is an update query.  For the example I just used the iif portion.

3 - i dont think that I used the wizard to create the form, however can't be positive, sometimes I use the wizard sometimes I do not.  
0
 
Steve BinkCommented:
It looks OK to me, but the real question is does it work for you?  If you are getting the results you need, then it's all good.  :)

My only other recommendation is to revisit jimhorn's suggestion at the top of this question.  The complexity of your IIF statement is already pretty high, and will just get worse as you find new fields to include.  You should consider making into a sub/function.
0
 
H-SCAuthor Commented:
routinet,

This seems to be working fine.  I will take a look at jimhorn's suggestion if I see that this is going to be a much more complex statement.

Thanks Again
0
 
aikimarkCommented:
I agree with routinet that you may be reaching a limit on the understandability (complexity) and maintainability of your Update statement and should consider replacing the IIF()/Switch() function with a user-defined function.

Since we now have a better idea of what you want, consider this:

Public Function FieldXLATE(parmField1, parmField2, parmField3) As String

    Select Case True
        Case Not (IsNull(parmField1) Or IsNull(parmField2) Or IsNull   (parmField3))
            FieldXLATE = "This Is A Test 1"
        Case IsNull(parmField1) And IsNull(parmField2) And IsNull(parmField3)
            FieldXLATE = "This Is A Test 2"
        Case IsNull(parmField1)
            FieldXLATE = "This Is A Test 3"
        Case IsNull(parmField2)
            FieldXLATE = "This Is A Test 4"
        Case IsNull(parmField3)
            FieldXLATE = "This Is A Test 5"
        Case Else
            FieldXLATE = "This Is An Unexpected Result"
    End Select

End Function
0
 
JonoBBCommented:
Aikmark,

My understanding is that VBA recognises IS NULL or isnull....i.e. you can use either
0
 
Steve BinkCommented:
JonoBB:  "IS NULL" is a piece of SQL nomenclature and will not be recognized as a VB function.  IsNull() is a VB function, which will be recognized by SQL only inside Access, or other VB-driven environments.  Otherwise, it is used strictly within VB code.
0
 
H-SCAuthor Commented:
Aikimark,

Will the case function work in Access (VB)?  In the past I have tried case satatements in Access without success.
0
 
aikimarkCommented:
MSAccess does NOT support the use of a CASE clause as part of a column definition in a SQL statement, unless it is a passthrough query to an external database.

The Select Case statement I wrote should be contained within a public function in your database.  

Usage example:
Update Utable
Set Utable.UField = FieldXLATE(Field_1, Field_2, Field_3)
0
 
H-SCAuthor Commented:
many thanks for everyones help.  I am currently using the code that I found on my system from past use :

IIf([Table_1!Field_1] Is Not Null And [Table_1!Field_2] Is Not Null And [Table_1!Field_3] Is Not Null,"This Is A Test 1",
IIf([Table_1!Field_1] Is Null And [Table_1!Field_2] Is Null And [Table_1!Field_3] Is Null,"This Is A Test 2",
IIf([Table_1!Field_1] Is Null,"This Is A Test 3",
IIf([Table_1!Field_2] Is Null,"This Is A Test 4",IIf([Table_1!Field_3] Is Null,"This Is A Test 5")))))

but feel like that I will eventually need to use one or more of the suggestions.  Not sure how things work from here on awarding points.  Any suggestions?

Again,
Thank You ALL..
 
0
 
Steve BinkCommented:
You have the option of splitting the points among the experts you felt have helped you.  Follow the link at the bottom of the question, then allocate the points in the next page among all the comments you have received.  The basic instructions on how to split the points can be found at:

http://www.experts-exchange.com/help.jsp#hi19
0
 
H-SCAuthor Commented:
routinet,

once again thank you for your help.  I will read the link and take your advice
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 8
  • 7
  • 5
  • +6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now