What is really going on with IIF

[Noob]

Hi,


I want to make sure I understand this, so any help here is appreciated.
OK so I have a little IIF (AND) statement.

       IIF( 
              [REF Dino]![Category]="Region" 
       AND    [REF Dino]![Dug Type]<>"Gen",[REF Form Info]![Region BagOf Bones]

That’s not all of it. Besides my question it more to do with the remaining values.
So in the above I have an IIF statement looking in my Dino TABLE at a FIELD called Category with any record set values that are = to "Region" AND in the DugType FIELD <> "GEN". Any records that match that criteria are going to be UPDATEd {That’s the TRUE part of this little ditty}
Now to my question. Is the rest of the data that gets filtered out either because it <> "Region" or it does = "Gen" go through whatever my FALSE part of the statement is? If so then do we deal with records in FIELDs as groups. Almost like we are going to iterate through all the records in that FIELD in that TABLE?
So on the front side of the argument we are doing something with any records = to "Region", but on the backside of the argument everything that <> "Region" is going to be filtered through as well, but only from the [Category] FIELD? Or are we going to also have a chance to deal with every record in the [Dug Type] FIELD as well.

Yeah I dont quite have my head wrapped around this yet.

TIA

Noob

[ranman256]

is this in a query?

[Noob]

Yes sorry I didnt mention that. Thanks for asking

[John_G]

How are you using this statement? The Iif function returns a value based on the parameters you give it - it does not by itself make any updates to existing data.

The Iif as you show it is incomplete and will cause a syntax error - what does the whole statement look like?

An Iif is not a domain function, that is it cannot look at all records in a table or query that meet specified criteria (unlike Excel's Iif)

Please provide more details on what it is you are trying to do.

[Noob]

The Iif as you show it is incomplete and will cause a syntax error - what does the whole statement look like?

UPDATE     [REF Dino],
        [REF Form Info] 
SET    [REF Dino].Bones = 
       IIF( 
              [REF Dino]![Category]="Region" 
       AND    [REF Dino]![Dug Type]<>"Gen",[REF Form Info]![Region BagOf Bones],
               IIF( 
                     [REF Dino]![Category] LIKE "*Spec*", 
                     IIF( 
                            ISNULL([REF Form Info]![WWF]),0.115,[REF Form Info]![WWF]),
                      IIF( 
                            [REF Dino]![Category]="Ret-30" 
                     AND    [REF Dino]![Dug Type]<>"Gen", 
                            IIF( 
                                   [REF Form Info]![Includes SSGs as BagOf?]="Yes",[REF Form Info]![Ret 30 BagOf Bones]-0.01,[REF Form Info]![Ret 30 BagOf Bones]),
                             IIF( 
                                   [REF Dino]![Category]="Ret-90" 
                            AND    [REF Dino]![Dug Type]<>"Gen", 
                                   IIF( 
                                          [REF Form Info]![Includes SSGs as BagOf?]="Yes",[REF Form Info]![Ret 90 BagOf Bones]-0.01,[REF Form Info]![Ret 90 BagOf Bones]),[REF Dino]![Bones])))),
        [REF Dino].[Discovery] = 
       IIF( 
              [REF Dino]![Category]="Region",[REF Form Info]![Region Discovery],
               IIF( 
                     [REF Dino]![Dug Type]="Gen", 
                     IIF( 
                            [REF Dino]![Category] LIKE "Ret*30",[REF Form Info]![Ret 30 Gen Discovery],[REF Form Info]![Ret 90 Gen Discovery]),
                      IIF( 
                            [REF Dino]![Category] LIKE "Ret*30",[REF Form Info]![Ret 30 BagOf Discovery],[REF Form Info]![Ret 90 BagOf Discovery])))
WHERE  ((( 
                            [REF Dino].Category)<>"Inj-E")));

Please provide more details on what it is you are trying to do.

Just trying to understand IIF & what its doing so that I can explain it to my managers. I got about 50 of these to do & they tell me its easy. I told them if they think its easy to do it. Its not in my wheel house.

I don't mind doing it. But I need to understand. I think I do & tried to outline what I understand & what I am a little unclear about in my 1st post.

I am just asking a question so I can better understand & fish on my own.

Is the remainder (FALSE) of the IIF statement ALL of the other Recordsets in that field that DOES NOT meet the TRUE requirements?
Does ALL of the Recordsets from the AND clause go through the FALSE part of the IIF meatGrinder?

[John_G]

First thing I notice is that while you have two tables ([Ref Dino] and [REF Form Info] in your UPDATE, you are not relating the tables with a join. The result is that the number of records in the query will be the product of the number of records in [Ref Dino] * the number of records in [REF Form Info]. If the latter has only one record, then there is not an issue.

In your update statement, each record in the query is looked at individually; all the table field references in any one row will refer to the same record, and only that record. In other words, the TRUE's and FALSE's are determined using data from only one record at a time.

You can always rewrite an IIf statement using the If...Then...Else construct in VBA.

[Noob]

Haha, Thanks for this. I get to share some knowledge with you.

First thing I notice is that while you have two tables ([Ref Dino] and [REF Form Info] in your UPDATE, you are not relating the tables with a join.

Its a Cartesian Query More Detail Here: Cartesian product ~"To create a Cartesian product, you do not want any lines connecting your tables: delete any that might be present"~

In your update statement, each record in the query is looked at individually; all the table field references in any one row will refer to the same record, and only that record. In other words, the TRUE's and FALSE's are determined using data from only one record at a time.

Right but the IIF statement is going to iterate through the whole FIELD looking to meet the condition.

You can always rewrite an IIf statement using the If...Then...Else construct in VBA.

Ahuh I am with you, But I would have to write a Loop to iterate through the FIELD. Right? And that what was kinda throwing me. The IIF automatically iterates through "X" I am guessing it isn’t limited to FIELDS

One more question. The AND part of the Clause doesnt iterate throught the whole FIELD though right? If the answer is Yes & No then I think I understand.
No NOT going to iterate through the FIELD.

 AND    [REF Dino]![Dug Type]<>"Gen"

Yes LOOKs through all the Recordsets in the "Dug Type" FIELD for a match with the AND Clause.

              [REF Dino]![Category]="Region" 
       AND    [REF Dino]![Dug Type]<>"Gen"

So what I am looking to verify is that I dont need to worry about the DugType Field on the other side (FALSE) of my IIF statement.

[orange]

Can I ask what exactly it is you are trying to achieve -- in plain English?

If this is a business issue/opportunity, then please describe it in simple terms.
It appears you have some code you don't understand. And that code may or may not be relevant to something you are working on.
Also, not many UPDATE queries involve multiple tables, and fewer with no joins.

If you are trying to build a cartesian product, then you are probably looking at INSERT INTO query.

The link in my signature will take you to functions and examples.
IIF()

Good luck.

[CJ_London]

@noob - just a tip when posting new questions based on previous threads - include a link. You are 8 posts in and no further forward because the same clarifying questions are being asked. Previous thread can be found here

https://www.accessforums.net/showthread.php?t=61429

[Noob]

Can I ask what exactly it is you are trying to achieve -- in plain English?

Understand IIF. I need to step through the logic in these Queries.
Ajax told me to work through the logic with a little data tree.
I started doing that, but I need to know what data is flowing to the FALSE part of the statement.

Maybe this will help you understand what I am asking. Here is my statement or piece of it. Here we are going to test EVERY record in the Category FIELD looking for the string "Region" AND in the Dug Type FIELD making sure they dont also have the string "Gen" if TRUE do X if FALSE do Y

       IIF( 
              [REF Dino]![Category]="Region" 
       AND    [REF Dino]![Dug Type]<>"Gen",

Now what if I swap the 1st test in the IF statement with the AND clause.

 IIF( 
[REF Dino]![Dug Type]<>"Gen" 
AND [REF Dino]![Category]="Region",

Is that now a New & completely different result? I am thinking so because we are NOT going to iterate through the AND clause, but over it. IF that makes any sense.

[Noob]

Thanks Ajax. Yeah I guess I should have done that but it is a differant question all together & didnt want to muddy the water.

[CJ_London]

google 'vba iif' to find out what the iif statement does - simplistically it is

iif(statement, if statement is true do this, if statement is false do that)

since

IIF( 
              [REF Dino]![Category]="Region" 
       AND    [REF Dino]![Dug Type]<>"Gen",

and

IIF( 
[REF Dino]![Dug Type]<>"Gen" 
AND [REF Dino]![Category]="Region",

is the same statement, it does not affect the outcome

Put it on one line for clarity

IIF([REF Dino]![Category]="Region" AND [REF Dino]![Dug Type]<>"Gen",

or

IIF([REF Dino]![Dug Type]<>"Gen" AND [REF Dino]![Category]="Region",

you can see there is no difference to whether the result is true or false

[Noob]

HUH!!! Well I'll be... THANK YOU so... MUCH!!! EVERYBODY!!! But specifically you @Ajax thank you for putting me out of my misery.