Solved

IIf statement

Posted on 2004-08-25
33
1,047 Views
Last Modified: 2012-05-05
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
Comment
Question by:H-SC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 5
  • +6
33 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 24 total points
ID: 11897211
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
 
LVL 34

Assisted Solution

by:flavo
flavo earned 23 total points
ID: 11897290


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
 
LVL 2

Assisted Solution

by:wblakely
wblakely earned 23 total points
ID: 11897297
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!

 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 23 total points
ID: 11897336
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
 
LVL 16

Assisted Solution

by:Nestorio
Nestorio earned 23 total points
ID: 11897346
I think 3rd part never will execute, because the null condition for both fields is trapped before.
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 23 total points
ID: 11898094
try:

IIf(IsNull([table1!field1]) ,"test1",
IIf(IsNull([table1!field2]) ,"test2",
IIf(IsNull([table1!field1]) And IsNull([table2!field2]) ,"testing123")))
0
 
LVL 16

Assisted Solution

by:GreymanMSC
GreymanMSC earned 23 total points
ID: 11898279
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
 
LVL 45

Expert Comment

by:aikimark
ID: 11898594
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
 
LVL 16

Expert Comment

by:Nestorio
ID: 11899220
Try this:

IIf(IsNull([table1!field1]) and IsNull([table2!field2]), "testing123", IIf(IsNull([table1!field1], "test1", IIf(IsNull([table1!field2], "test2")))
0
 
LVL 16

Expert Comment

by:GreymanMSC
ID: 11900010
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
 
LVL 8

Assisted Solution

by:JonoBB
JonoBB earned 23 total points
ID: 11900545
Access has a much better way of doing all of this - using the Switch function, rather than nexted iff's.

Check it out.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 11901484
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
 
LVL 16

Expert Comment

by:Nestorio
ID: 11901714
Thanks Greyman, you're right:

Please JonoBB, could you explain the use of the Switch function?
0
 
LVL 8

Expert Comment

by:JonoBB
ID: 11901775
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
 
LVL 16

Expert Comment

by:Nestorio
ID: 11901858
OK, I see, it's a clearer sintax than IIf in this case. Thanks.
0
 
LVL 8

Expert Comment

by:JonoBB
ID: 11901881
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
 
LVL 45

Expert Comment

by:aikimark
ID: 11902752
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11903430
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
 
LVL 1

Author Comment

by:H-SC
ID: 11903645
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
 
LVL 45

Expert Comment

by:aikimark
ID: 11903801
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
 
LVL 1

Author Comment

by:H-SC
ID: 11945598
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
 
LVL 45

Expert Comment

by:aikimark
ID: 11946076
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
 
LVL 1

Author Comment

by:H-SC
ID: 11946259
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11946272
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
 
LVL 1

Author Comment

by:H-SC
ID: 11946388
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
 
LVL 45

Expert Comment

by:aikimark
ID: 11946911
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
 
LVL 8

Expert Comment

by:JonoBB
ID: 11949971
Aikmark,

My understanding is that VBA recognises IS NULL or isnull....i.e. you can use either
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11953663
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
 
LVL 1

Author Comment

by:H-SC
ID: 11956016
Aikimark,

Will the case function work in Access (VB)?  In the past I have tried case satatements in Access without success.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 11956841
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
 
LVL 1

Author Comment

by:H-SC
ID: 11996971
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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12001077
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
 
LVL 1

Author Comment

by:H-SC
ID: 12005712
routinet,

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

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question