IIF: any suggestion?

[rio]

I have the following columns;
ID, OPS, CO, HC.

Example:

ID OPS CO HC RESULT
1 Static 2 500
2 Static 5 400 5
3 Static 5 660 5 AND 660
4 Static 3 700 700

What I need is to create a new column (RESULT) in query. This RESULT column will show CO if more than 4 or HC if more than 600 or both if both more than limit.

 

[Mike375]

See how this goes for you.

Result: IIf([CO]>4 And [HC]>600,[CO] & " " & "And" & " " & [HC],IIf([CO]<=4 And [HC]>600,[HC],IIf([CO]>4 And [HC]<=600,[CO])))

 

[rio]

See how this goes for you.

Result: IIf([CO]>4 And [HC]>600,[CO] & " " & "And" & " " & [HC],IIf([CO]<=4 And [HC]>600,[HC],IIf([CO]>4 And [HC]<=600,[CO])))

Thanks Mike375...... it work g8.

 

[stopher]

It's also worth mentioning the SWITCH function which tends to be easier to write than nested IIF statements:

SWITCH(
[CO]>4 And [HC]>600,[CO] & " " & "And" & " " & [HC],
[CO]<=4 And [HC]>600,[HC],
[CO]>4 And [HC]<=600,[CO])

Chris

 

[rio]

It's also worth mentioning the SWITCH function which tends to be easier to write than nested IIF statements:

SWITCH(
[CO]>4 And [HC]>600,[CO] & " " & "And" & " " & [HC],
[CO]<=4 And [HC]>600,[HC],
[CO]>4 And [HC]<=600,[CO])

Chris

Thanks...Stopher. before this i only used IIF. now u give me new knowledge. can u elaborate more about SWITCH.

 

[Mike375]

Chris's is not on line at the moment and is probably in bed since he is in the UK.

Here is omething from a website

Switch (OwnerID=1, "Table", OwnerID=2, "Desk", OwnerID=15, "Mirrow")

returns:
"Table", if the OwnerID field is 1.
"Desk", if the OwnerID field is 2.
"Mirrow", if the OwnerID field is 15.

From memory I think Switch can do up to 7 items.

I am not sure why it is not more widely used. In my own case there were problems/bugs with it in Access 95 and so I have just stuck with IIF.

As you can see from the example, it is easier to write than nested IIF. Although for many Access users nested IIF is easy since they will already have them done. As an example, that one I gave you, I simply took one of my own and changed the field names and conditions and posted it. In other words, all the IIFs, brackets and commas were already there.

 

[wiklendt]

It's also worth mentioning the SWITCH function which tends to be easier to write than nested IIF statements:

SWITCH(
[CO]>4 And [HC]>600,[CO] & " " & "And" & " " & [HC],
[CO]<=4 And [HC]>600,[HC],
[CO]>4 And [HC]<=600,[CO])

Chris

..ooooooooo!!! like rio, i've never heard of switch() either.... nice!


...and this is the first time i put 1 + 1 = 2 re your name! (chris + stopher)! LOL

 

[rio]

Chris's is not on line at the moment and is probably in bed since he is in the UK.

Here is omething from a website

Switch (OwnerID=1, "Table", OwnerID=2, "Desk", OwnerID=15, "Mirrow")

returns:
"Table", if the OwnerID field is 1.
"Desk", if the OwnerID field is 2.
"Mirrow", if the OwnerID field is 15.

From memory I think Switch can do up to 7 items.

I am not sure why it is not more widely used. In my own case there were problems/bugs with it in Access 95 and so I have just stuck with IIF.

As you can see from the example, it is easier to write than nested IIF. Although for many Access users nested IIF is easy since they will already have them done. As an example, that one I gave you, I simply took one of my own and changed the field names and conditions and posted it. In other words, all the IIFs, brackets and commas were already there.

Yup... I agree with u. both can used 4 different reason. so when is the right time we should used IIF or SWITCH.

as a beginner, I like to learn more....more and more.

 

[Mike375]

I would suggest you do a Google search on any of the Access functions whether they be Left(), Switch() and so on and you will turn up sites that list all of them and the various uses. Unfortunately I don't have such a link handy at the moment, but there are plenty of them.

There maybe a speed difference between the two, I don't know and that would only show up with lots of records and if you had a few fields based on either Switch or IIF. Switch exits on the first correct answer.

I suspect mastering IIF is probably better if you are using forums like this a lot as IIF will be the answer virtually everytime.

 

[rio]

Ok.... Thanks 4 ur Suggestion.

 

[Mike375]

I just turned up the site that is good for functions

http://www.webcheatsheet.com/SQL/access_functions/Switch.php

That will open on Switch and you will see all the others listed on the left. That should keep you busy for awhile

 

[JohnB]

Switch is brilliant, but you should finish it off with a statement which will return a 'catch all' result if the previous statements don't trigger a response ie Switch (OwnerID=1, "Table", OwnerID=2, "Desk", OwnerID=15, "Mirrow",True,0) Hope this helps.

 

[stopher]

...and this is the first time i put 1 + 1 = 2 re your name! (chris + stopher)! LOL

It started when I acted as chauffeur for my brothers wedding. I got the nickname "stopher the chauffeur"

 

[stopher]

Yup... I agree with u. both can used 4 different reason. so when is the right time we should used IIF or SWITCH.

Personally I would never nest more than two IIF functions. If I needed to nest more than two IIF functions then I would always use SWITCH. Of course it is personal preference though.

Chris(stopher)

 

[wiklendt]

Personally I would never nest more than two IIF functions. If I needed to nest more than two IIF functions then I would always use SWITCH. Of course it is personal preference though.

Chris(stopher)

don't both functions have a limit of 7, though? what do you do if you need more? (i.e., 7 nests for ifs and 7 arguments for switch)

 

[Mike375]

don't both functions have a limit of 7, though? what do you do if you need more? (i.e., 7 nests for ifs and 7 arguments for switch)

You can use a field that is as a result of an IIF for a value in another IIF field.

But perhaps once you get to that level it is better to do Case Select or an If Then on a form or have the variables in another table.

 

[stopher]

If I needed to nest more than two IIF functions then I would always use SWITCH.

Actually I was slightly wrong here, I would normally look to resolving multiple ifs of the "lookup variety by using a table and a join query.

OwnerID Furniture
1 "Table"
2 "Desk"
15 "Mirrow"

If we are talking about resolving complex logic expressions then I would consider writing a custom VBA function and use the CASE statement.

So in reality I don't usually face the problem of deep nesting.

don't both functions have a limit of 7, though? what do you do if you need more? (i.e., 7 nests for ifs and 7 arguments for switch)

Would you seriously consider writing an expression with 7 nested IIFs? I tried a SWITCH with 14 levels and it worked fine. At 15 I got the message "the expression you entered is too complex access".

The the point is if you are having to do to anything so complex then consider a table or CASE in VBA where you have much better clarity in what you are designing.

Chris