Solved

IIf statement

Posted on 2004-08-25
33
1,045 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
  • 8
  • 7
  • 5
  • +6
33 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 24 total points
Comment Utility
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
Comment Utility


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
Comment Utility
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 50

Assisted Solution

by:Steve Bink
Steve Bink earned 23 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Greyman, you're right:

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

Expert Comment

by:JonoBB
Comment Utility
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
Comment Utility
OK, I see, it's a clearer sintax than IIf in this case. Thanks.
0
 
LVL 8

Expert Comment

by:JonoBB
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
routinet,

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now