Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# IIf statement

Posted on 2004-08-25
Medium Priority
1,050 Views
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
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
• 8
• 7
• 5
• +6

LVL 66

Accepted Solution

Jim Horn earned 96 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

---------------
MyValue = MyTest([table1!field1], [table2!field2] )

Hope this helps.
-Jim
0

LVL 34

Assisted Solution

flavo earned 92 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

wblakely earned 92 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

LVL 51

Assisted Solution

Steve Bink earned 92 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

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

LVL 46

Assisted Solution

aikimark earned 92 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

GreymanMSC earned 92 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 46

Expert Comment

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

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

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

JonoBB earned 92 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 46

Expert Comment

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

ID: 11901714
Thanks Greyman, you're right:

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

LVL 8

Expert Comment

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

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

LVL 8

Expert Comment

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 46

Expert Comment

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 51

Expert Comment

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

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 46

Expert Comment

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

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 46

Expert Comment

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

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 51

Expert Comment

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

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 46

Expert Comment

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

ID: 11949971
Aikmark,

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

LVL 51

Expert Comment

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

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 46

Expert Comment

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

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 51

Expert Comment

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

ID: 12005712
routinet,

0

## Featured Post

Question has a verified solution.

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

Preparing an email is something we should all take special care with â€“ especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challenâ€¦
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance andâ€¦
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all recordâ€¦
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 â€¦
###### Suggested Courses
Course of the Month5 days, 15 hours left to enroll